summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/create_drop_view.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/create_drop_view.test')
-rw-r--r--mysql-test/main/create_drop_view.test29
1 files changed, 29 insertions, 0 deletions
diff --git a/mysql-test/main/create_drop_view.test b/mysql-test/main/create_drop_view.test
new file mode 100644
index 00000000..0ebf643e
--- /dev/null
+++ b/mysql-test/main/create_drop_view.test
@@ -0,0 +1,29 @@
+CREATE TABLE t1(id INT);
+CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 WHERE id>10;
+INSERT INTO t1 VALUES (5), (8), (10), (20), (30);
+SELECT * FROM t1;
+SELECT * FROM v1;
+
+--error ER_TABLE_EXISTS_ERROR
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE id>11;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+
+CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 WHERE id>12;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 WHERE id>13;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE id>14;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+
+INSERT INTO t1 VALUES (50), (80), (3), (2), (40);
+SELECT * FROM t1;
+SELECT * FROM v1;
+
+DROP TABLE IF EXISTS v1;
+DROP VIEW IF EXISTS v1;
+DROP VIEW IF EXISTS v1;
+DROP VIEW IF EXISTS t1;
+DROP TABLE t1;