You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
112 lines
6.3 KiB
112 lines
6.3 KiB
5 months ago
|
#
|
||
|
|
# Bug#40277 SHOW CREATE VIEW returns invalid SQL
|
||
|
|
# Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list
|
||
|
|
#
|
||
|
|
# 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement.
|
||
|
|
# Constant with length = 65 . Expect to get the identifier 'Name_exp_1'.
|
||
|
|
CREATE VIEW v1 AS SELECT '<--- 65 char including the arrows --->';
|
||
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1' ORDER BY COLUMN_NAME;
|
||
|
|
COLUMN_NAME
|
||
|
|
Name_exp_1
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'.
|
||
|
|
# Attention: Identifier for the column within the subquery will be not generated.
|
||
|
|
CREATE VIEW v1 AS SELECT (SELECT '<--- 54 char including the arrows (+ 11 outside) -->');
|
||
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1' ORDER BY COLUMN_NAME;
|
||
|
|
COLUMN_NAME
|
||
|
|
Name_exp_1
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# -----------------------------------------------------------------------------------------------------------------
|
||
|
|
# 64 characters are the maximum length of a column identifier. The system can derive the name from the statement.
|
||
|
|
CREATE VIEW v1 AS SELECT '<--- 64 char including the arrows --->';
|
||
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1' ORDER BY COLUMN_NAME;
|
||
|
|
COLUMN_NAME
|
||
|
|
<--- 64 char including the arrows --->
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS SELECT (SELECT '<--- 53 char including the arrows (+ 11 outside) --->');
|
||
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1' ORDER BY COLUMN_NAME;
|
||
|
|
COLUMN_NAME
|
||
|
|
(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# -----------------------------------------------------------------------------------------------------------------
|
||
|
|
# Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space.
|
||
|
|
# Generated identifiers have at their end the position within the select column list.
|
||
|
|
# 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2'
|
||
|
|
CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
|
||
|
|
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1' ORDER BY COLUMN_NAME;
|
||
|
|
COLUMN_NAME
|
||
|
|
c1
|
||
|
|
c3
|
||
|
|
Name_exp_2
|
||
|
|
Name_exp_4
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
#
|
||
|
|
# Bug#40277 SHOW CREATE VIEW returns invalid SQL
|
||
|
|
#
|
||
|
|
DROP VIEW IF EXISTS v1;
|
||
|
|
DROP TABLE IF EXISTS t1,t2;
|
||
|
|
# Column name exceeds the maximum length.
|
||
|
|
CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# Column names with leading trailing spaces.
|
||
|
|
CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# Column name conflicts with a auto-generated one.
|
||
|
|
CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2';
|
||
|
|
DROP VIEW v1;
|
||
|
|
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`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
# Invalid conlumn name in subquery.
|
||
|
|
CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`;
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE TABLE t1(a INT);
|
||
|
|
CREATE TABLE t2 LIKE t1;
|
||
|
|
# Test alias in subquery
|
||
|
|
CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
|
||
|
|
DROP VIEW v1;
|
||
|
|
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));
|
||
|
|
DROP VIEW v1;
|
||
|
|
# 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;
|
||
|
|
View Create View character_set_client collation_connection
|
||
|
|
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 `alias`) utf8mb4 utf8mb4_0900_ai_ci
|
||
|
|
DROP VIEW v1;
|
||
|
|
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 `alias`);
|
||
|
|
DROP VIEW v1;
|
||
|
|
# 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;
|
||
|
|
View Create View character_set_client collation_connection
|
||
|
|
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 `alias`) utf8mb4 utf8mb4_0900_ai_ci
|
||
|
|
DROP VIEW v1;
|
||
|
|
CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by `alias`);
|
||
|
|
DROP VIEW v1;
|
||
|
|
DROP TABLE t1, t2;
|