diff options
Diffstat (limited to 'mysql-test/main/reopen_temp_table.test')
-rw-r--r-- | mysql-test/main/reopen_temp_table.test | 184 |
1 files changed, 184 insertions, 0 deletions
diff --git a/mysql-test/main/reopen_temp_table.test b/mysql-test/main/reopen_temp_table.test new file mode 100644 index 00000000000..2aa6caa1655 --- /dev/null +++ b/mysql-test/main/reopen_temp_table.test @@ -0,0 +1,184 @@ +--source include/have_innodb.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; |