1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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;
|
