| blob | 72c4bf29f253f75ada45beeaef399bf5c0d4c613 |
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'.
7 CREATE VIEW v1 AS SELECT '<--- 65 char including the arrows --->';
8 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
9 COLUMN_NAME
10 Name_exp_1
11 DROP VIEW v1;
12 CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
13 DROP VIEW v1;
14 CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
15 DROP 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.
18 CREATE VIEW v1 AS SELECT (SELECT '<--- 54 char including the arrows (+ 11 outside) -->');
19 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
20 COLUMN_NAME
21 Name_exp_1
22 DROP VIEW v1;
23 CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
24 DROP VIEW v1;
25 CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
26 DROP VIEW v1;
27 # -----------------------------------------------------------------------------------------------------------------
28 # 64 characters are the maximum length of a column identifier. The system can derive the name from the statement.
29 CREATE VIEW v1 AS SELECT '<--- 64 char including the arrows --->';
30 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
31 COLUMN_NAME
32 <--- 64 char including the arrows --->
33 DROP VIEW v1;
34 CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
35 DROP VIEW v1;
36 CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
37 DROP VIEW v1;
38 CREATE VIEW v1 AS SELECT (SELECT '<--- 53 char including the arrows (+ 11 outside) --->');
39 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
40 COLUMN_NAME
41 (SELECT '<--- 53 char including the arrows (+ 11 outside) --->')
42 DROP VIEW v1;
43 CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
44 DROP VIEW v1;
45 CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
46 DROP 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'
51 CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
52 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
53 COLUMN_NAME
54 c1
55 Name_exp_2
56 c3
57 Name_exp_4
58 DROP VIEW v1;
59 CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
60 DROP VIEW v1;
61 CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
62 DROP VIEW v1;
63 #
64 # Bug#40277 SHOW CREATE VIEW returns invalid SQL
65 #
66 DROP VIEW IF EXISTS v1;
67 DROP TABLE IF EXISTS t1,t2;
68 # Column name exceeds the maximum length.
69 CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
70 DROP VIEW v1;
71 CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`;
72 DROP VIEW v1;
73 # Column names with leading trailing spaces.
74 CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
75 DROP VIEW v1;
76 CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
77 DROP VIEW v1;
78 # Column name conflicts with a auto-generated one.
79 CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2';
80 DROP VIEW v1;
81 CREATE 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`;
82 DROP VIEW v1;
83 # Invalid conlumn name in subquery.
84 CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
85 DROP VIEW v1;
86 CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`;
87 DROP VIEW v1;
88 CREATE TABLE t1(a INT);
89 CREATE TABLE t2 LIKE t1;
90 # Test alias in subquery
91 CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
92 DROP VIEW v1;
93 CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where (`b`.`a` = 0));
94 DROP VIEW v1;
95 # Test column alias in subquery
96 CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias);
97 SHOW CREATE VIEW v1;
98 View Create View character_set_client collation_connection
99 v1 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`) latin1 latin1_swedish_ci
100 DROP VIEW v1;
101 CREATE 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`);
102 DROP VIEW v1;
103 # Alias as the expression column name.
104 CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias);
105 SHOW CREATE VIEW v1;
106 View Create View character_set_client collation_connection
107 v1 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 ') latin1 latin1_swedish_ci
108 DROP VIEW v1;
109 CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a ');
110 DROP VIEW v1;
111 DROP TABLE t1, t2;