1--source include/have_innodb.inc 2 3--echo # 4--echo # MDEV-5535: Cannot reopen temporary table 5--echo # 6 7--disable_warnings 8DROP DATABASE IF EXISTS temp_db; 9--enable_warnings 10 11CREATE DATABASE temp_db; 12USE temp_db; 13 14--echo # 15--echo # Reopen temporary table 16--echo # 17 18CREATE TEMPORARY TABLE t1(i int)ENGINE=INNODB; 19INSERT INTO t1 VALUES(1), (2); 20SELECT * FROM t1 a, t1 b; 21DROP TABLE t1; 22 23--echo # 24--echo # CREATE & Stored routines 25--echo # 26 27DELIMITER |; 28CREATE FUNCTION f1() RETURNS INT 29BEGIN 30 DROP TEMPORARY TABLE t1; 31 RETURN 1; 32END| 33DELIMITER ;| 34--error ER_BAD_TABLE_ERROR 35CREATE TEMPORARY TABLE t1 AS SELECT f1(); 36DROP FUNCTION f1; 37 38DELIMITER |; 39CREATE FUNCTION f2() RETURNS INT 40BEGIN 41 CREATE TEMPORARY TABLE t2(i INT); 42 INSERT INTO t2 VALUES(1), (2); 43 RETURN 1; 44END| 45DELIMITER ;| 46--error ER_TABLE_EXISTS_ERROR 47CREATE TEMPORARY TABLE t2 AS SELECT f2(); 48SELECT * FROM t2; 49DROP TABLE t2; 50DROP FUNCTION f2; 51 52CREATE TEMPORARY TABLE t3 AS SELECT 1 AS a; 53DELIMITER |; 54CREATE PROCEDURE p1() 55BEGIN 56 DROP TEMPORARY TABLE t3; 57end| 58CREATE FUNCTION f3() RETURNS INT 59BEGIN 60 CALL p1(); 61 RETURN 1; 62END| 63DELIMITER ;| 64PREPARE STMT FROM "SELECT f3() AS my_Column, a FROM t3"; 65--error ER_CANT_REOPEN_TABLE 66EXECUTE STMT; 67DROP TABLE t3; 68DROP FUNCTION f3; 69DROP PROCEDURE p1; 70 71CREATE TEMPORARY TABLE t4 (i INT); 72INSERT INTO t4 VALUES(1), (2); 73DELIMITER |; 74CREATE FUNCTION f4() RETURNS INT 75BEGIN 76 DROP TEMPORARY TABLE t4; 77 RETURN 1; 78END| 79DELIMITER ;| 80--error ER_CANT_REOPEN_TABLE 81SELECT f4() FROM t4; 82SELECT * FROM t4; 83DROP TABLE t4; 84DROP FUNCTION f4; 85 86CREATE TEMPORARY TABLE t5 AS SELECT 1 AS a; 87DELIMITER |; 88CREATE PROCEDURE p2() 89BEGIN 90 DROP TEMPORARY TABLE t5; 91END| 92CREATE FUNCTION f5() RETURNS INT 93BEGIN 94 CALL p2(); 95 RETURN 1; 96END| 97DELIMITER ;| 98--error ER_CANT_REOPEN_TABLE 99SELECT f5() AS my_column, a FROM t5; 100DROP TABLE t5; 101DROP FUNCTION f5; 102DROP PROCEDURE p2; 103 104--echo # 105--echo # CTAS 106--echo # 107 108CREATE TABLE t1(i INT); 109INSERT INTO t1 VALUES(1), (2); 110CREATE TEMPORARY TABLE t1 111 SELECT temp_1.i a, temp_2.i b FROM t1 AS temp_1, t1 AS temp_2; 112SELECT * FROM t1; 113DROP TABLE t1; 114SELECT * FROM t1; 115DROP TABLE t1; 116 117--echo # 118--echo # HANDLER 119--echo # 120 121CREATE TABLE t1 (a INT, KEY a(a)); 122INSERT INTO t1 (a) VALUES (1), (2), (3), (4), (5); 123CREATE TABLE t2 (a INT, KEY a (a)) SELECT * FROM t1; 124CREATE TEMPORARY TABLE t3 (a INT, KEY a (a)) SELECT * FROM t2; 125HANDLER t3 OPEN; 126SELECT * FROM t1; 127LOCK TABLE t1 READ; 128--error ER_LOCK_OR_ACTIVE_TRANSACTION 129HANDLER t3 OPEN; 130UNLOCK TABLES; 131--error ER_NONUNIQ_TABLE 132HANDLER t3 OPEN; 133HANDLER t3 READ NEXT; 134HANDLER t3 OPEN AS t3_1; 135HANDLER t3_1 READ NEXT; 136HANDLER t3_1 READ NEXT; 137HANDLER t3 CLOSE; 138HANDLER t3_1 CLOSE; 139DROP TEMPORARY TABLE t3; 140DROP TABLE t1, t2; 141 142--echo # 143--echo # INSERT-SELECT 144--echo # 145 146CREATE TEMPORARY TABLE t4 (a INT) ENGINE=MYISAM; 147INSERT INTO t4 VALUES(1), (2); 148INSERT INTO t4 SELECT * FROM t4; 149SELECT COUNT(*) FROM t4; 150DROP TABLE t4; 151 152CREATE TABLE t5 (a INT) ENGINE=INNODB; 153CREATE TEMPORARY TABLE t6 (a INT) ENGINE=INNODB; 154INSERT INTO t5 VALUES(1), (2); 155INSERT INTO t6 SELECT * FROM t5; 156INSERT INTO t6 SELECT * FROM t6; 157INSERT INTO t5 SELECT * FROM t6; 158SELECT COUNT(*)=6 FROM t5; 159SELECT COUNT(*)=4 FROM t6; 160DROP TABLE t5, t6; 161 162--echo # 163--echo # MDEV-10216: Assertion `strcmp(share->unique_file_name,filename) || 164--echo # share->last_version' failed in myisam/mi_open.c:67: test_if_reopen 165--echo # 166CREATE TEMPORARY TABLE t7 (i INT) ENGINE=MYISAM; 167INSERT INTO t7 VALUES(1); 168ALTER TABLE t7 RENAME TO t; 169SELECT * FROM t a, t b; 170DROP TABLE t; 171 172CREATE TEMPORARY TABLE t7 (i INT) ENGINE=ARIA; 173INSERT INTO t7 VALUES(1); 174ALTER TABLE t7 RENAME TO t; 175SELECT * FROM t a, t b; 176DROP TABLE t; 177 178CREATE TEMPORARY TABLE t8 (i INT) ENGINE=ARIA; 179ALTER TABLE t8 rename to t; 180SELECT (SELECT 1 FROM t a1, t a2 ) AS f1, ( SELECT 2 FROM t a3 ) AS f2 FROM DUAL; 181DROP TABLE t; 182 183--echo # Cleanup 184DROP DATABASE temp_db; 185USE test; 186 187# 188# 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 189# 190create temporary table t1 (f char(255), b int, index(b)) engine=MyISAM; 191replace into t1 values (null,1),(null,2); 192alter table t1 add fulltext key(f); 193--error ER_ALTER_OPERATION_NOT_SUPPORTED 194alter table t1 change if exists a b int, algorithm=inplace; 195check table t1; 196select * from t1; 197drop table t1; 198 199create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=1; 200replace into t1 values (null,1),(null,2); 201alter table t1 add fulltext key(f); 202--error ER_ALTER_OPERATION_NOT_SUPPORTED 203alter table t1 change if exists a b int, algorithm=inplace; 204check table t1; 205select * from t1; 206drop table t1; 207 208create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=page; 209replace into t1 values (null,1),(null,2); 210alter table t1 add fulltext key(f); 211--error ER_ALTER_OPERATION_NOT_SUPPORTED 212alter table t1 change if exists a b int, algorithm=inplace; 213check table t1; 214select * from t1; 215drop table t1; 216 217create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=dynamic; 218replace into t1 values (null,1),(null,2); 219alter table t1 add fulltext key(f); 220--error ER_ALTER_OPERATION_NOT_SUPPORTED 221alter table t1 change if exists a b int, algorithm=inplace; 222check table t1; 223select * from t1; 224drop table t1; 225