Debian Codegraph @

mysql-test/suite/sysschema/r/pr_table_exists.result

raw
1 CREATE TABLE t1 (id INT PRIMARY KEY);
2 CALL sys.table_exists('test', 't1', @exists);
3 SELECT @exists;
4 @exists
5 BASE TABLE
6 DROP TABLE t1;
7 CREATE view v_t1 AS SELECT 1;
8 CALL sys.table_exists('test', 'v_t1', @exists);
9 SELECT @exists;
10 @exists
11 VIEW
12 DROP VIEW v_t1;
13 CREATE TABLE tv (i int) with system versioning;
14 CALL sys.table_exists('test','tv',@exists);
15 SELECT @exists;
16 @exists
17 BASE TABLE
18 DROP TABLE tv;
19 CREATE SEQUENCE s;
20 CALL sys.table_exists('test','s',@exists);
21 SELECT @exists;
22 @exists
23 SEQUENCE
24 DROP SEQUENCE s;
25 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
26 CALL sys.table_exists('test', 't1', @exists);
27 SELECT @exists;
28 @exists
29 TEMPORARY
30 DROP TEMPORARY TABLE t1;
31 CALL sys.table_exists('information_schema', 'all_plugins', @exists);
32 SELECT @exists;
33 @exists
34 SYSTEM VIEW
35 CALL sys.table_exists('test', 't2', @exists);
36 SELECT @exists;
37 @exists
38
39 SET @identifier := REPEAT('a', 65);
40 CALL sys.table_exists(@identifier, 't1', @exists);
41 ERROR 22001: Data too long for column 'in_db' at row 1
42 CALL sys.table_exists('test', @identifier, @exists);
43 ERROR 22001: Data too long for column 'in_table' at row 0
44 SET @identifier := NULL;
45 #
46 # MDEV-28391: table_exists procedure fails with
47 # Incorrect table name with backtick identifiers
48 #
49 CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
50 CALL sys.table_exists('test', 'ab`c', @tbl_type);
51 SELECT @tbl_type;
52 @tbl_type
53 BASE TABLE
54 DROP TABLE `ab``c`;
55 CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
56 CALL sys.table_exists('test', 'ab`c', @tbl_type);
57 SELECT @tbl_type;
58 @tbl_type
59 TEMPORARY
60 DROP TABLE `ab``c`;
61 CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
62 CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
63 CALL sys.table_exists('test', 'ab`c', @tbl_type);
64 SELECT @tbl_type;
65 @tbl_type
66 TEMPORARY
67 # We cannot send quoted identifer to the procedure, no table will be found
68 CALL sys.table_exists('test', '`ab``c`', @tbl_type);
69 SELECT @tbl_type;
70 @tbl_type
71
72 # Remove temporary table
73 DROP TABLE `ab``c`;
74 CALL sys.table_exists('test', 'ab`c', @tbl_type);
75 SELECT @tbl_type;
76 @tbl_type
77 BASE TABLE
78 # Remove base table
79 DROP TABLE `ab``c`;
80 # MDEV-12459: The information_schema tables for getting temporary tables
81 # info is missing, at least for innodb, there is no
82 # INNODB_TEMP_TABLE_INFO
83 #
84 CREATE TABLE t1 (id INT PRIMARY KEY);
85 CALL sys.table_exists('test', 't1', @exists);
86 SELECT @exists;
87 @exists
88 BASE TABLE
89 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
90 CALL sys.table_exists('test', 't1', @exists);
91 SELECT @exists;
92 @exists
93 TEMPORARY
94 DROP TEMPORARY TABLE t1;
95 DROP TABLE t1;
96 #
97 # MDEV-28335: TABLE_TYPE for temporary sequences
98 # is the same as for permanent ones
99 #
100 CREATE TEMPORARY SEQUENCE s1;
101 CALL sys.table_exists('test', 's1', @exists);
102 SELECT @exists;
103 @exists
104 TEMPORARY SEQUENCE
105 DROP SEQUENCE s1;
106 CREATE SEQUENCE s;
107 CALL sys.table_exists('test', 's', @exists);
108 SELECT @exists;
109 @exists
110 SEQUENCE
111 CREATE TEMPORARY SEQUENCE s;
112 CALL sys.table_exists('test', 's', @exists);
113 SELECT @exists;
114 @exists
115 TEMPORARY
116 DROP SEQUENCE s;
117 CALL sys.table_exists('test', 's', @exists);
118 SELECT @exists;
119 @exists
120 SEQUENCE
121 DROP SEQUENCE s;
122 CALL sys.table_exists('test', 's', @exists);
123 SELECT @exists;
124 @exists
125
126 CREATE TEMPORARY SEQUENCE t1;
127 CALL sys.table_exists('test', 't1', @exists);
128 SELECT @exists;
129 @exists
130 TEMPORARY SEQUENCE
131 CREATE TABLE t1 (id INT PRIMARY KEY);
132 CALL sys.table_exists('test', 't1', @exists);
133 SELECT @exists;
134 @exists
135 TEMPORARY
136 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
137 ERROR 42S01: Table 't1' already exists
138 CREATE SEQUENCE t1;
139 ERROR 42S01: Table 't1' already exists
140 DROP SEQUENCE t1;
141 CALL sys.table_exists('test', 't1', @exists);
142 SELECT @exists;
143 @exists
144 BASE TABLE
145 CREATE SEQUENCE t1;
146 ERROR 42S01: Table 't1' already exists
147 CREATE TEMPORARY SEQUENCE t1;
148 CALL sys.table_exists('test', 't1', @exists);
149 SELECT @exists;
150 @exists
151 TEMPORARY
152 DROP SEQUENCE t1;
153 CALL sys.table_exists('test', 't1', @exists);
154 SELECT @exists;
155 @exists
156 BASE TABLE
157 CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
158 CALL sys.table_exists('test', 't1', @exists);
159 SELECT @exists;
160 @exists
161 TEMPORARY
162 CREATE TEMPORARY SEQUENCE t1;
163 ERROR 42S01: Table 't1' already exists
164 CREATE SEQUENCE t1;
165 ERROR 42S01: Table 't1' already exists
166 DROP TEMPORARY TABLE t1;
167 DROP TABLE t1;
168 CREATE SEQUENCE t1;
169 CALL sys.table_exists('test', 't1', @exists);
170 SELECT @exists;
171 @exists
172 SEQUENCE
173 CREATE TEMPORARY TABLE t1(t int);
174 CALL sys.table_exists('test', 't1', @exists);
175 SELECT @exists;
176 @exists
177 TEMPORARY
178 DROP TABLE t1;
179 CALL sys.table_exists('test', 't1', @exists);
180 SELECT @exists;
181 @exists
182 SEQUENCE
183 CREATE TEMPORARY SEQUENCE t1;
184 CALL sys.table_exists('test', 't1', @exists);
185 SELECT @exists;
186 @exists
187 TEMPORARY
188 DROP TABLE t1;
189 DROP TABLE t1;