1# 2# Bug#40277 SHOW CREATE VIEW returns invalid SQL 3# Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list 4# 5# 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement. 6# Constant with length = 65 . Expect to get the identifier 'Name_exp_1'. 7CREATE VIEW v1 AS SELECT '<--- 65 char including the arrows --->'; 8SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; 9COLUMN_NAME 10Name_exp_1 11DROP VIEW v1; 12CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`; 13DROP VIEW v1; 14CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`; 15DROP VIEW v1; 16# Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'. 17# Attention: Identifier for the column within the subquery will be not generated. 18CREATE VIEW v1 AS SELECT (SELECT '<--- 54 char including the arrows (+ 11 outside) -->'); 19SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; 20COLUMN_NAME 21Name_exp_1 22DROP VIEW v1; 23CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`; 24DROP VIEW v1; 25CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`; 26DROP VIEW v1; 27# ----------------------------------------------------------------------------------------------------------------- 28# 64 characters are the maximum length of a column identifier. The system can derive the name from the statement. 29CREATE VIEW v1 AS SELECT '<--- 64 char including the arrows --->'; 30SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; 31COLUMN_NAME 32<--- 64 char including the arrows ---> 33DROP VIEW v1; 34CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`; 35DROP VIEW v1; 36CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`; 37DROP VIEW v1; 38CREATE VIEW v1 AS SELECT (SELECT '<--- 53 char including the arrows (+ 11 outside) --->'); 39SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; 40COLUMN_NAME 41(SELECT '<--- 53 char including the arrows (+ 11 outside) --->') 42DROP VIEW v1; 43CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`; 44DROP VIEW v1; 45CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`; 46DROP VIEW v1; 47# ----------------------------------------------------------------------------------------------------------------- 48# Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space. 49# Generated identifiers have at their end the position within the select column list. 50# 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2' 51CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 '; 52SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; 53COLUMN_NAME 54c1 55Name_exp_2 56c3 57Name_exp_4 58DROP VIEW v1; 59CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; 60DROP VIEW v1; 61CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; 62DROP VIEW v1; 63# 64# Bug#40277 SHOW CREATE VIEW returns invalid SQL 65# 66DROP VIEW IF EXISTS v1; 67DROP TABLE IF EXISTS t1,t2; 68# Column name exceeds the maximum length. 69CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555'; 70DROP VIEW v1; 71CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`; 72DROP VIEW v1; 73# Column names with leading trailing spaces. 74CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 '; 75DROP VIEW v1; 76CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; 77DROP VIEW v1; 78# Column name conflicts with a auto-generated one. 79CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2'; 80DROP VIEW v1; 81CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`,'Name_exp_2' AS `My_exp_Name_exp_2`; 82DROP VIEW v1; 83# Invalid conlumn name in subquery. 84CREATE VIEW v1 AS SELECT (SELECT ' c1 '); 85DROP VIEW v1; 86CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`; 87DROP VIEW v1; 88CREATE TABLE t1(a INT); 89CREATE TABLE t2 LIKE t1; 90# Test alias in subquery 91CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0); 92DROP VIEW v1; 93CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where `b`.`a` = 0 limit 1); 94DROP VIEW v1; 95# Test column alias in subquery 96CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias); 97SHOW CREATE VIEW v1; 98View Create View character_set_client collation_connection 99v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select `t1`.`a` AS `alias` from `t1` group by `t1`.`a` limit 1) latin1 latin1_swedish_ci 100DROP VIEW v1; 101CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select `test`.`t1`.`a` AS `alias` from `test`.`t1` group by `test`.`t1`.`a` limit 1); 102DROP VIEW v1; 103# Alias as the expression column name. 104CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias); 105SHOW CREATE VIEW v1; 106View Create View character_set_client collation_connection 107v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select ' a ' AS `alias` from `t1` group by ' a ' limit 1) latin1 latin1_swedish_ci 108DROP VIEW v1; 109CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a ' limit 1); 110DROP VIEW v1; 111DROP TABLE t1, t2; 112create view v1 as select interval(55,10) as my_col; 113show create view v1; 114View Create View character_set_client collation_connection 115v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select interval(55,10) AS `my_col` latin1 latin1_swedish_ci 116select * from v1; 117my_col 1181 119drop view v1; 120