Debian Codegraph @

mysql-test/suite/sysschema/t/pr_table_exists.test

raw
1 -- source include/not_embedded.inc
2
3
4 # Create a base table and a view
5 CREATE TABLE t1 (id INT PRIMARY KEY);
6 # Verify the base table and view is supported
7 CALL sys.table_exists('test', 't1', @exists);
8 SELECT @exists;
9 DROP TABLE t1;
10
11 CREATE view v_t1 AS SELECT 1;
12 CALL sys.table_exists('test', 'v_t1', @exists);
13 SELECT @exists;
14 DROP VIEW v_t1;
15
16 CREATE TABLE tv (i int) with system versioning;
17 CALL sys.table_exists('test','tv',@exists);
18 SELECT @exists;
19 DROP TABLE tv;
20
21 CREATE SEQUENCE s;
22 CALL sys.table_exists('test','s',@exists);
23 SELECT @exists;
24 DROP SEQUENCE s;
25
26 # Replace the base table with a temporary table
27 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
28 CALL sys.table_exists('test', 't1', @exists);
29 SELECT @exists;
30 DROP TEMPORARY TABLE t1;
31
32 CALL sys.table_exists('information_schema', 'all_plugins', @exists);
33 SELECT @exists;
34
35 # Try a non-existing table
36 CALL sys.table_exists('test', 't2', @exists);
37 SELECT @exists;
38
39 # Try variables longer than expected
40 SET @identifier := REPEAT('a', 65);
41
42 -- error 1406
43 CALL sys.table_exists(@identifier, 't1', @exists);
44
45 -- error 1406
46 CALL sys.table_exists('test', @identifier, @exists);
47
48 SET @identifier := NULL;
49
50 --echo #
51 --echo # MDEV-28391: table_exists procedure fails with
52 --echo # Incorrect table name with backtick identifiers
53 --echo #
54 CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
55 CALL sys.table_exists('test', 'ab`c', @tbl_type);
56 SELECT @tbl_type;
57 DROP TABLE `ab``c`;
58 CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
59 CALL sys.table_exists('test', 'ab`c', @tbl_type);
60 SELECT @tbl_type;
61 DROP TABLE `ab``c`;
62 CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
63 CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
64 CALL sys.table_exists('test', 'ab`c', @tbl_type);
65 SELECT @tbl_type;
66 --echo # We cannot send quoted identifer to the procedure, no table will be found
67 CALL sys.table_exists('test', '`ab``c`', @tbl_type);
68 SELECT @tbl_type;
69 --echo # Remove temporary table
70 DROP TABLE `ab``c`;
71 CALL sys.table_exists('test', 'ab`c', @tbl_type);
72 SELECT @tbl_type;
73 --echo # Remove base table
74 DROP TABLE `ab``c`;
75 --echo # MDEV-12459: The information_schema tables for getting temporary tables
76 --echo # info is missing, at least for innodb, there is no
77 --echo # INNODB_TEMP_TABLE_INFO
78 --echo #
79
80 # Temporary table will shadow the base table without warning
81 CREATE TABLE t1 (id INT PRIMARY KEY);
82 # Verify the base table and view is supported
83 CALL sys.table_exists('test', 't1', @exists);
84 SELECT @exists;
85 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
86 CALL sys.table_exists('test', 't1', @exists);
87 SELECT @exists;
88
89 DROP TEMPORARY TABLE t1;
90 DROP TABLE t1;
91
92 --echo #
93 --echo # MDEV-28335: TABLE_TYPE for temporary sequences
94 --echo # is the same as for permanent ones
95 --echo #
96
97 CREATE TEMPORARY SEQUENCE s1;
98 CALL sys.table_exists('test', 's1', @exists);
99 # If there is no shadowing with temporary table, result is table type
100 SELECT @exists;
101 DROP SEQUENCE s1;
102 CREATE SEQUENCE s;
103 CALL sys.table_exists('test', 's', @exists); SELECT @exists;
104 # Creating temporary sequence over sequence results in `temporary`
105 CREATE TEMPORARY SEQUENCE s;
106 CALL sys.table_exists('test', 's', @exists); SELECT @exists;
107 # First drop temporary sequence
108 DROP SEQUENCE s;
109 CALL sys.table_exists('test', 's', @exists); SELECT @exists;
110 DROP SEQUENCE s;
111 CALL sys.table_exists('test', 's', @exists); SELECT @exists;
112
113 CREATE TEMPORARY SEQUENCE t1;
114 CALL sys.table_exists('test', 't1', @exists);
115 SELECT @exists;
116 CREATE TABLE t1 (id INT PRIMARY KEY);
117 CALL sys.table_exists('test', 't1', @exists);
118 # Before was a `temporary sequence`, now should be `temporary`
119 SELECT @exists;
120 # It is not possible to create temporary table over temporary sequence with the same name
121 --error ER_TABLE_EXISTS_ERROR
122 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
123 # It is not possible to create sequence over temporary sequence with the same name
124 --error ER_TABLE_EXISTS_ERROR
125 CREATE SEQUENCE t1;
126 # This will drop temporary sequence
127 DROP SEQUENCE t1;
128 CALL sys.table_exists('test', 't1', @exists);
129 # This will lead to `base table`
130 SELECT @exists;
131 # It is not possible to create a sequence over the base table
132 --error ER_TABLE_EXISTS_ERROR
133 CREATE SEQUENCE t1;
134 # Let's test with temporary sequence instead
135 CREATE TEMPORARY SEQUENCE t1;
136 CALL sys.table_exists('test', 't1', @exists);
137 # Will return temporary as expected
138 SELECT @exists;
139 # Again droping the temporary sequence
140 DROP SEQUENCE t1;
141 # Will lead to the base table
142 CALL sys.table_exists('test', 't1', @exists);
143 SELECT @exists;
144 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
145 # This will return `temporary`
146 CALL sys.table_exists('test', 't1', @exists);
147 SELECT @exists;
148 # We cannot create temporary sequence over temporary table
149 --error ER_TABLE_EXISTS_ERROR
150 CREATE TEMPORARY SEQUENCE t1;
151 --error ER_TABLE_EXISTS_ERROR
152 CREATE SEQUENCE t1;
153 DROP TEMPORARY TABLE t1;
154 # Drop base table
155 DROP TABLE t1;
156 CREATE SEQUENCE t1;
157 CALL sys.table_exists('test', 't1', @exists);
158 # Should be a sequence
159 SELECT @exists;
160 # Create an temporary table
161 CREATE TEMPORARY TABLE t1(t int);
162 CALL sys.table_exists('test', 't1', @exists);
163 # Should shadow an sequence with temporary
164 SELECT @exists;
165 # Drop temporary table
166 DROP TABLE t1;
167 CALL sys.table_exists('test', 't1', @exists);
168 # Should again show the sequence
169 SELECT @exists;
170 CREATE TEMPORARY SEQUENCE t1;
171 CALL sys.table_exists('test', 't1', @exists);
172 # Should shadow an sequence with temporary
173 SELECT @exists;
174 # Drop temporary sequence
175 DROP TABLE t1;
176 # Drop an sequence
177 DROP TABLE t1;