summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/reopen_temp_table.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/reopen_temp_table.test
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/reopen_temp_table.test')
-rw-r--r--mysql-test/main/reopen_temp_table.test228
1 files changed, 228 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 00000000..3d621fbb
--- /dev/null
+++ b/mysql-test/main/reopen_temp_table.test
@@ -0,0 +1,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;