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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
-- source include/not_embedded.inc
# Create a base table and a view
CREATE TABLE t1 (id INT PRIMARY KEY);
# Verify the base table and view is supported
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
DROP TABLE t1;
CREATE view v_t1 AS SELECT 1;
CALL sys.table_exists('test', 'v_t1', @exists);
SELECT @exists;
DROP VIEW v_t1;
CREATE TABLE tv (i int) with system versioning;
CALL sys.table_exists('test','tv',@exists);
SELECT @exists;
DROP TABLE tv;
CREATE SEQUENCE s;
CALL sys.table_exists('test','s',@exists);
SELECT @exists;
DROP SEQUENCE s;
# Replace the base table with a temporary table
CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
DROP TEMPORARY TABLE t1;
CALL sys.table_exists('information_schema', 'all_plugins', @exists);
SELECT @exists;
# Try a non-existing table
CALL sys.table_exists('test', 't2', @exists);
SELECT @exists;
# Try variables longer than expected
SET @identifier := REPEAT('a', 65);
-- error 1406
CALL sys.table_exists(@identifier, 't1', @exists);
-- error 1406
CALL sys.table_exists('test', @identifier, @exists);
SET @identifier := NULL;
--echo #
--echo # MDEV-28391: table_exists procedure fails with
--echo # Incorrect table name with backtick identifiers
--echo #
CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
CALL sys.table_exists('test', 'ab`c', @tbl_type);
SELECT @tbl_type;
DROP TABLE `ab``c`;
CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
CALL sys.table_exists('test', 'ab`c', @tbl_type);
SELECT @tbl_type;
DROP TABLE `ab``c`;
CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
CALL sys.table_exists('test', 'ab`c', @tbl_type);
SELECT @tbl_type;
--echo # We cannot send quoted identifer to the procedure, no table will be found
CALL sys.table_exists('test', '`ab``c`', @tbl_type);
SELECT @tbl_type;
--echo # Remove temporary table
DROP TABLE `ab``c`;
CALL sys.table_exists('test', 'ab`c', @tbl_type);
SELECT @tbl_type;
--echo # Remove base table
DROP TABLE `ab``c`;
--echo # MDEV-12459: The information_schema tables for getting temporary tables
--echo # info is missing, at least for innodb, there is no
--echo # INNODB_TEMP_TABLE_INFO
--echo #
# Temporary table will shadow the base table without warning
CREATE TABLE t1 (id INT PRIMARY KEY);
# Verify the base table and view is supported
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
DROP TEMPORARY TABLE t1;
DROP TABLE t1;
--echo #
--echo # MDEV-28335: TABLE_TYPE for temporary sequences
--echo # is the same as for permanent ones
--echo #
CREATE TEMPORARY SEQUENCE s1;
CALL sys.table_exists('test', 's1', @exists);
# If there is no shadowing with temporary table, result is table type
SELECT @exists;
DROP SEQUENCE s1;
CREATE SEQUENCE s;
CALL sys.table_exists('test', 's', @exists); SELECT @exists;
# Creating temporary sequence over sequence results in `temporary`
CREATE TEMPORARY SEQUENCE s;
CALL sys.table_exists('test', 's', @exists); SELECT @exists;
# First drop temporary sequence
DROP SEQUENCE s;
CALL sys.table_exists('test', 's', @exists); SELECT @exists;
DROP SEQUENCE s;
CALL sys.table_exists('test', 's', @exists); SELECT @exists;
CREATE TEMPORARY SEQUENCE t1;
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
CREATE TABLE t1 (id INT PRIMARY KEY);
CALL sys.table_exists('test', 't1', @exists);
# Before was a `temporary sequence`, now should be `temporary`
SELECT @exists;
# It is not possible to create temporary table over temporary sequence with the same name
--error ER_TABLE_EXISTS_ERROR
CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
# It is not possible to create sequence over temporary sequence with the same name
--error ER_TABLE_EXISTS_ERROR
CREATE SEQUENCE t1;
# This will drop temporary sequence
DROP SEQUENCE t1;
CALL sys.table_exists('test', 't1', @exists);
# This will lead to `base table`
SELECT @exists;
# It is not possible to create a sequence over the base table
--error ER_TABLE_EXISTS_ERROR
CREATE SEQUENCE t1;
# Let's test with temporary sequence instead
CREATE TEMPORARY SEQUENCE t1;
CALL sys.table_exists('test', 't1', @exists);
# Will return temporary as expected
SELECT @exists;
# Again droping the temporary sequence
DROP SEQUENCE t1;
# Will lead to the base table
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
# This will return `temporary`
CALL sys.table_exists('test', 't1', @exists);
SELECT @exists;
# We cannot create temporary sequence over temporary table
--error ER_TABLE_EXISTS_ERROR
CREATE TEMPORARY SEQUENCE t1;
--error ER_TABLE_EXISTS_ERROR
CREATE SEQUENCE t1;
DROP TEMPORARY TABLE t1;
# Drop base table
DROP TABLE t1;
CREATE SEQUENCE t1;
CALL sys.table_exists('test', 't1', @exists);
# Should be a sequence
SELECT @exists;
# Create an temporary table
CREATE TEMPORARY TABLE t1(t int);
CALL sys.table_exists('test', 't1', @exists);
# Should shadow an sequence with temporary
SELECT @exists;
# Drop temporary table
DROP TABLE t1;
CALL sys.table_exists('test', 't1', @exists);
# Should again show the sequence
SELECT @exists;
CREATE TEMPORARY SEQUENCE t1;
CALL sys.table_exists('test', 't1', @exists);
# Should shadow an sequence with temporary
SELECT @exists;
# Drop temporary sequence
DROP TABLE t1;
# Drop an sequence
DROP TABLE t1;
|
