Create 'main' test directory and move 't' and 'r' there

1 files changed, 101 insertions, 0 deletions

diff --git a/mysql-test/main/view_alias.test b/mysql-test/main/view_alias.test
new file mode 100644
index 00000000000..09d707296bb
--- /dev/null
+++ b/mysql-test/main/view_alias.test
@@ -0,0 +1,101 @@
+--echo #
+--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL
+--echo # Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list
+--echo #
+
+--echo # 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement.
+--echo #    Constant with length = 65 . Expect to get the identifier 'Name_exp_1'.
+let $after_select= '<--- 65 char including the arrows                            --->';
+--source include/view_alias.inc
+--echo #    Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'.
+--echo #    Attention: Identifier for the column within the subquery will be not generated.
+let $after_select=  (SELECT '<--- 54 char including the arrows (+ 11 outside)   -->');
+--source include/view_alias.inc
+--echo # -----------------------------------------------------------------------------------------------------------------
+#
+--echo # 64 characters are the maximum length of a column identifier. The system can derive the name from the statement.
+let $after_select= '<--- 64 char including the arrows                           --->';
+--source include/view_alias.inc
+let $after_select=  (SELECT '<--- 53 char including the arrows (+ 11 outside) --->');
+--source include/view_alias.inc
+--echo # -----------------------------------------------------------------------------------------------------------------
+#
+--echo # Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space.
+--echo # Generated identifiers have at their end the position within the select column list.
+--echo # 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2'
+let $after_select= 'c1', 'c2 ', ' c3', ' c4 ';
+--source include/view_alias.inc
+
+--echo #
+--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL
+--echo #
+
+--disable_warnings
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+--echo # Column name exceeds the maximum length.
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Column names with leading trailing spaces.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Column name conflicts with a auto-generated one.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Invalid conlumn name in subquery.
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2 LIKE t1;
+
+--echo # Test alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Test column alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Alias as the expression column name.
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT '  a   ' AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+DROP TABLE t1, t2;
+
+#
+# MDEV-7482 VIEW containing INTERVAL(...)
+#
+create view v1 as select interval(55,10) as my_col;
+show create view v1;
+select * from v1;
+drop view v1;
+