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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
--source include/have_innodb.inc
#Temporary table is only available to the current session
#and the view cannot be created based on the temporary table
--source include/no_view_protocol.inc
--echo #
--echo # MDEV-5535: Cannot reopen temporary table
--echo #
--disable_warnings
DROP DATABASE IF EXISTS temp_db;
--enable_warnings
CREATE DATABASE temp_db;
USE temp_db;
--echo #
--echo # Reopen temporary table
--echo #
CREATE TEMPORARY TABLE t1(i int)ENGINE=INNODB;
INSERT INTO t1 VALUES(1), (2);
SELECT * FROM t1 a, t1 b;
DROP TABLE t1;
--echo #
--echo # CREATE & Stored routines
--echo #
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
DROP TEMPORARY TABLE t1;
RETURN 1;
END|
DELIMITER ;|
--error ER_BAD_TABLE_ERROR
CREATE TEMPORARY TABLE t1 AS SELECT f1();
DROP FUNCTION f1;
DELIMITER |;
CREATE FUNCTION f2() RETURNS INT
BEGIN
CREATE TEMPORARY TABLE t2(i INT);
INSERT INTO t2 VALUES(1), (2);
RETURN 1;
END|
DELIMITER ;|
--error ER_TABLE_EXISTS_ERROR
CREATE TEMPORARY TABLE t2 AS SELECT f2();
SELECT * FROM t2;
DROP TABLE t2;
DROP FUNCTION f2;
CREATE TEMPORARY TABLE t3 AS SELECT 1 AS a;
DELIMITER |;
CREATE PROCEDURE p1()
BEGIN
DROP TEMPORARY TABLE t3;
end|
CREATE FUNCTION f3() RETURNS INT
BEGIN
CALL p1();
RETURN 1;
END|
DELIMITER ;|
PREPARE STMT FROM "SELECT f3() AS my_Column, a FROM t3";
--error ER_CANT_REOPEN_TABLE
EXECUTE STMT;
DROP TABLE t3;
DROP FUNCTION f3;
DROP PROCEDURE p1;
CREATE TEMPORARY TABLE t4 (i INT);
INSERT INTO t4 VALUES(1), (2);
DELIMITER |;
CREATE FUNCTION f4() RETURNS INT
BEGIN
DROP TEMPORARY TABLE t4;
RETURN 1;
END|
DELIMITER ;|
--error ER_CANT_REOPEN_TABLE
SELECT f4() FROM t4;
SELECT * FROM t4;
DROP TABLE t4;
DROP FUNCTION f4;
CREATE TEMPORARY TABLE t5 AS SELECT 1 AS a;
DELIMITER |;
CREATE PROCEDURE p2()
BEGIN
DROP TEMPORARY TABLE t5;
END|
CREATE FUNCTION f5() RETURNS INT
BEGIN
CALL p2();
RETURN 1;
END|
DELIMITER ;|
--error ER_CANT_REOPEN_TABLE
SELECT f5() AS my_column, a FROM t5;
DROP TABLE t5;
DROP FUNCTION f5;
DROP PROCEDURE p2;
--echo #
--echo # CTAS
--echo #
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES(1), (2);
CREATE TEMPORARY TABLE t1
SELECT temp_1.i a, temp_2.i b FROM t1 AS temp_1, t1 AS temp_2;
SELECT * FROM t1;
DROP TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
--echo #
--echo # HANDLER
--echo #
CREATE TABLE t1 (a INT, KEY a(a));
INSERT INTO t1 (a) VALUES (1), (2), (3), (4), (5);
CREATE TABLE t2 (a INT, KEY a (a)) SELECT * FROM t1;
CREATE TEMPORARY TABLE t3 (a INT, KEY a (a)) SELECT * FROM t2;
HANDLER t3 OPEN;
SELECT * FROM t1;
LOCK TABLE t1 READ;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
HANDLER t3 OPEN;
UNLOCK TABLES;
--error ER_NONUNIQ_TABLE
HANDLER t3 OPEN;
HANDLER t3 READ NEXT;
HANDLER t3 OPEN AS t3_1;
HANDLER t3_1 READ NEXT;
HANDLER t3_1 READ NEXT;
HANDLER t3 CLOSE;
HANDLER t3_1 CLOSE;
DROP TEMPORARY TABLE t3;
DROP TABLE t1, t2;
--echo #
--echo # INSERT-SELECT
--echo #
CREATE TEMPORARY TABLE t4 (a INT) ENGINE=MYISAM;
INSERT INTO t4 VALUES(1), (2);
INSERT INTO t4 SELECT * FROM t4;
SELECT COUNT(*) FROM t4;
DROP TABLE t4;
CREATE TABLE t5 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE t6 (a INT) ENGINE=INNODB;
INSERT INTO t5 VALUES(1), (2);
INSERT INTO t6 SELECT * FROM t5;
INSERT INTO t6 SELECT * FROM t6;
INSERT INTO t5 SELECT * FROM t6;
SELECT COUNT(*)=6 FROM t5;
SELECT COUNT(*)=4 FROM t6;
DROP TABLE t5, t6;
--echo #
--echo # MDEV-10216: Assertion `strcmp(share->unique_file_name,filename) ||
--echo # share->last_version' failed in myisam/mi_open.c:67: test_if_reopen
--echo #
CREATE TEMPORARY TABLE t7 (i INT) ENGINE=MYISAM;
INSERT INTO t7 VALUES(1);
ALTER TABLE t7 RENAME TO t;
SELECT * FROM t a, t b;
DROP TABLE t;
CREATE TEMPORARY TABLE t7 (i INT) ENGINE=ARIA;
INSERT INTO t7 VALUES(1);
ALTER TABLE t7 RENAME TO t;
SELECT * FROM t a, t b;
DROP TABLE t;
CREATE TEMPORARY TABLE t8 (i INT) ENGINE=ARIA;
ALTER TABLE t8 rename to t;
SELECT (SELECT 1 FROM t a1, t a2 ) AS f1, ( SELECT 2 FROM t a3 ) AS f2 FROM DUAL;
DROP TABLE t;
--echo # Cleanup
DROP DATABASE temp_db;
USE test;
#
# MDEV-17070 Table corruption or Assertion `table->file->stats.records > 0 || error' or Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon actions on temporary table
#
create temporary table t1 (f char(255), b int, index(b)) engine=MyISAM;
replace into t1 values (null,1),(null,2);
alter table t1 add fulltext key(f);
--error ER_ALTER_OPERATION_NOT_SUPPORTED
alter table t1 change if exists a b int, algorithm=inplace;
check table t1;
select * from t1;
drop table t1;
create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=1;
replace into t1 values (null,1),(null,2);
alter table t1 add fulltext key(f);
--error ER_ALTER_OPERATION_NOT_SUPPORTED
alter table t1 change if exists a b int, algorithm=inplace;
check table t1;
select * from t1;
drop table t1;
create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=page;
replace into t1 values (null,1),(null,2);
alter table t1 add fulltext key(f);
--error ER_ALTER_OPERATION_NOT_SUPPORTED
alter table t1 change if exists a b int, algorithm=inplace;
check table t1;
select * from t1;
drop table t1;
create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=dynamic;
replace into t1 values (null,1),(null,2);
alter table t1 add fulltext key(f);
--error ER_ALTER_OPERATION_NOT_SUPPORTED
alter table t1 change if exists a b int, algorithm=inplace;
check table t1;
select * from t1;
drop table t1;
|
