summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/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/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/temp_table.test')
-rw-r--r--mysql-test/main/temp_table.test700
1 files changed, 700 insertions, 0 deletions
diff --git a/mysql-test/main/temp_table.test b/mysql-test/main/temp_table.test
new file mode 100644
index 00000000..6ed5dbd8
--- /dev/null
+++ b/mysql-test/main/temp_table.test
@@ -0,0 +1,700 @@
+# mysqltest should be fixed
+--source include/not_embedded.inc
+--source include/have_innodb.inc
+
+#View cannot has temporary table in definition
+--source include/no_view_protocol.inc
+
+#
+# Test of temporary tables
+#
+
+--echo #
+--echo # test basic creation of temporary tables together with normal table
+--echo #
+
+create table t1 (a int);
+create temporary table t1 AS SELECT 1;
+--error 1050
+create temporary table t1 AS SELECT 1;
+--error 1050
+create temporary table t1 (a int);
+drop temporary table t1;
+drop table t1;
+
+create temporary table t1 AS SELECT 1;
+--error 1050
+create temporary table t1 AS SELECT 1;
+--error 1050
+create temporary table t1 (a int);
+drop temporary table t1;
+
+--echo #
+--echo # Test with rename
+--echo #
+
+CREATE TABLE t1 (c int not null, d char (10) not null);
+insert into t1 values(1,""),(2,"a"),(3,"b");
+CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
+insert into t1 values(4,"e"),(5,"f"),(6,"g");
+alter table t1 rename t2;
+select * from t1;
+select * from t2;
+CREATE TABLE t2 (x int not null, y int not null);
+alter table t2 rename t1;
+select * from t1;
+create TEMPORARY TABLE t2 engine=heap select * from t1;
+create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap;
+
+# This should give errors
+--error 1050
+CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
+--error 1050
+ALTER TABLE t1 RENAME t2;
+
+select * from t2;
+alter table t2 add primary key (a,b);
+drop table t1,t2;
+select * from t1;
+drop table t2;
+create temporary table t1 select *,2 as "e" from t1;
+select * from t1;
+drop table t1;
+drop table t1;
+
+#
+# Test CONCAT_WS with temporary tables
+#
+
+CREATE TABLE t1 (pkCrash INTEGER PRIMARY KEY,strCrash VARCHAR(255));
+INSERT INTO t1 ( pkCrash, strCrash ) VALUES ( 1, '1');
+SELECT CONCAT_WS(pkCrash, strCrash) FROM t1;
+drop table t1;
+create temporary table t1 select 1 as 'x';
+drop table t1;
+CREATE TABLE t1 (x INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TEMPORARY TABLE tmp SELECT *, NULL FROM t1;
+drop table t1;
+
+#
+# Problem with ELT
+#
+create temporary table t1 (id int(10) not null unique);
+create temporary table t2 (id int(10) not null primary key,
+val int(10) not null);
+
+# put in some initial values
+insert into t1 values (1),(2),(4);
+insert into t2 values (1,1),(2,1),(3,1),(4,2);
+
+# do a query using ELT, a join and an ORDER BY.
+select one.id, two.val, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id;
+drop table t1,t2;
+
+#
+# Test of failed ALTER TABLE on temporary table
+#
+create temporary table t1 (a int not null);
+insert into t1 values (1),(1);
+-- error ER_DUP_ENTRY
+alter table t1 add primary key (a);
+drop table t1;
+
+#
+# In MySQL 4.0.4 doing a GROUP BY on a NULL column created a disk based
+# temporary table when a memory based one would be good enough.
+
+CREATE TABLE t1 (
+ d datetime default NULL
+) ENGINE=MyISAM;
+
+
+INSERT INTO t1 VALUES ('2002-10-24 14:50:32'),('2002-10-24 14:50:33'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40');
+
+flush status;
+--disable_ps_protocol
+select * from t1 group by d;
+show status like "created_tmp%tables";
+--enable_ps_protocol
+drop table t1;
+
+# Fix for BUG#8921: Check that temporary table is ingored by view commands.
+create temporary table v1 as select 'This is temp. table' A;
+create view v1 as select 'This is view' A;
+select * from v1;
+show create table v1;
+show create view v1;
+drop view v1;
+select * from v1;
+create view v1 as select 'This is view again' A;
+select * from v1;
+drop table v1;
+select * from v1;
+drop view v1;
+
+# Bug #8497: tmpdir with extra slashes would cause failures
+#
+create table t1 (a int, b int, index(a), index(b));
+create table t2 (c int auto_increment, d varchar(255), primary key (c));
+insert into t1 values (3,1),(3,2);
+insert into t2 values (NULL, 'foo'), (NULL, 'bar');
+select d, c from t1 left join t2 on b = c where a = 3 order by d;
+drop table t1, t2;
+
+
+#
+# BUG#21096: locking issue ; temporary table conflicts.
+#
+# The problem was that on DROP TEMPORARY table name lock was acquired,
+# which should not be done.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+LOCK TABLE t1 WRITE;
+
+connect (conn1, localhost, root,,);
+
+CREATE TEMPORARY TABLE t1 (i INT);
+
+--echo The following command should not block
+DROP TEMPORARY TABLE t1;
+
+disconnect conn1;
+connection default;
+
+DROP TABLE t1;
+
+#
+# Check that it's not possible to drop a base table with
+# DROP TEMPORARY statement.
+#
+CREATE TABLE t1 (i INT);
+CREATE TEMPORARY TABLE t2 (i INT);
+
+--error 1051
+DROP TEMPORARY TABLE t2, t1;
+
+# Table t2 should have been dropped.
+--error 1146
+SELECT * FROM t2;
+# But table t1 should still be there.
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+
+--echo End of 4.1 tests.
+
+#
+# Bug #24791: Union with AVG-groups generates wrong results
+#
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+SELECT c/1 FROM t1 UNION SELECT 1;
+
+DROP TABLE t1, t2;
+
+#
+# Test truncate with temporary tables
+#
+
+create temporary table t1 (a int);
+insert into t1 values (4711);
+select * from t1;
+truncate t1;
+insert into t1 values (42);
+select * from t1;
+drop table t1;
+
+#
+# Bug #35392: Delete all statement does not execute properly after
+# few delete statements
+#
+CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20));
+INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3');
+DELETE FROM t1 WHERE a=1;
+SELECT count(*) FROM t1;
+DELETE FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+#
+# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE TEMPORARY TABLE t1 (a INT);
+CREATE TEMPORARY TABLE t2 LIKE t1;
+
+DELIMITER |;
+CREATE FUNCTION f1() RETURNS INT
+ BEGIN
+ return 1;
+ END|
+DELIMITER ;|
+
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t1 SELECT f1();
+
+CREATE TABLE t3 SELECT * FROM t1;
+INSERT INTO t1 SELECT f1();
+
+UPDATE t1,t2 SET t1.a = t2.a;
+INSERT INTO t2 SELECT f1();
+
+DROP TABLE t1,t2,t3;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Bug #48067: A temp table with the same name as an existing table,
+--echo # makes drop database fail.
+--echo #
+--disable_warnings
+DROP TEMPORARY TABLE IF EXISTS bug48067.t1;
+DROP DATABASE IF EXISTS bug48067;
+--enable_warnings
+CREATE DATABASE bug48067;
+CREATE TABLE bug48067.t1 (c1 int);
+INSERT INTO bug48067.t1 values (1);
+CREATE TEMPORARY TABLE bug48067.t1 (c1 int);
+DROP DATABASE bug48067;
+DROP TEMPORARY table bug48067.t1;
+
+--echo End of 5.1 tests
+
+--echo #
+--echo # Test that admin statements work for temporary tables.
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+CREATE TEMPORARY TABLE t1(a INT);
+CREATE TEMPORARY TABLE t2(b INT);
+CREATE TEMPORARY TABLE t3(c INT);
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (11), (12), (13);
+INSERT INTO t3 VALUES (101), (102), (103);
+
+ANALYZE TABLE t1, t2, t3;
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (11), (12), (13);
+INSERT INTO t3 VALUES (101), (102), (103);
+
+CHECK TABLE t1, t2, t3;
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (11), (12), (13);
+INSERT INTO t3 VALUES (101), (102), (103);
+
+--replace_column 2 xxx
+CHECKSUM TABLE t1, t2, t3;
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (11), (12), (13);
+INSERT INTO t3 VALUES (101), (102), (103);
+
+OPTIMIZE TABLE t1, t2, t3;
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (11), (12), (13);
+INSERT INTO t3 VALUES (101), (102), (103);
+
+REPAIR TABLE t1, t2, t3;
+
+DROP TABLES t1, t2, t3;
+
+CREATE TEMPORARY TABLE t1 (a int);
+RENAME TABLE t1 TO t2;
+DROP TABLE t2;
+
+#
+# CREATE TEMPORARY TEMPORARY TABLE
+#
+--error ER_PARSE_ERROR
+create temporary temporary table t1 (a int);
+
+#
+# MDEV-7832 Add status variables to track CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE
+#
+flush status;
+create table t1 (a int);
+create temporary table t2 (a int);
+create temporary table t3 (a int);
+drop table t1;
+drop table t2;
+drop temporary table t3;
+show status like 'com_create%table';
+show status like 'com_drop%table';
+
+--echo #
+--echo # Some more generic temporary table tests
+--echo # added during MDEV-5535.
+--echo #
+
+--disable_warnings
+DROP DATABASE IF EXISTS temp_db;
+--enable_warnings
+
+CREATE DATABASE temp_db;
+USE temp_db;
+
+--echo #
+--echo # SHOW TABLES do not list temporary tables.
+--echo #
+
+CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+INSERT INTO temp_t1 VALUES(1);
+SELECT * FROM temp_t1;
+SHOW TABLES;
+DROP TABLE temp_t1;
+
+--echo #
+--echo # Create and drop a temporary table.
+--echo #
+
+CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+INSERT INTO temp_t1 VALUES(1);
+SELECT * FROM temp_t1;
+DROP TABLE temp_t1;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM temp_t1;
+
+--echo #
+--echo # Create a temporary table and base table with same name and DROP TABLE.
+--echo #
+
+CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+INSERT INTO t1 VALUES("BASE TABLE");
+# Temporary table shadows the base table with the same name.
+CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+INSERT INTO t1 VALUES("TEMPORARY TABLE");
+SELECT * FROM t1;
+# Only temporary table should get dropped.
+DROP TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t1;
+
+--echo #
+--echo # Create a temporary table and base table with same name and DROP TEMPORARY
+--echo # TABLE.
+--echo #
+
+CREATE TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+INSERT INTO t1 VALUES("BASE TABLE");
+# Temporary table shadows the base table with the same name.
+CREATE TEMPORARY TABLE t1(c1 VARCHAR(20)) ENGINE=INNODB;
+INSERT INTO t1 VALUES("TEMPORARY TABLE");
+SELECT * FROM t1;
+# Only temporary table should get dropped.
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+--error ER_BAD_TABLE_ERROR
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Create a temporary table and drop its parent database.
+--echo #
+
+USE temp_db;
+CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+INSERT INTO temp_t1 VALUES (1);
+# Drop database
+DROP DATABASE temp_db;
+CREATE DATABASE temp_db;
+USE temp_db;
+# Temporary tables are not physically tied to schemas
+DROP TEMPORARY TABLE temp_t1;
+
+--echo #
+--echo # Similar to above, but this time with a base table with same name.
+--echo #
+
+USE temp_db;
+CREATE TABLE t1(i INT)ENGINE=INNODB;
+CREATE TEMPORARY TABLE t1(i INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1);
+# Drop database
+DROP DATABASE temp_db;
+CREATE DATABASE temp_db;
+USE temp_db;
+# Temporary tables are not physically tied to schemas
+DROP TEMPORARY TABLE t1;
+--error ER_BAD_TABLE_ERROR
+DROP TABLE t1;
+
+--echo #
+--echo # Create a temporary table within a function.
+--echo #
+
+USE temp_db;
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+ DROP TEMPORARY TABLE IF EXISTS temp_t1;
+ CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+ INSERT INTO `temp_t1` VALUES(1);
+ RETURN (SELECT COUNT(*) FROM temp_t1);
+END|
+delimiter ;|
+
+SELECT f1();
+SELECT * FROM temp_t1;
+
+DROP TABLE temp_t1;
+CREATE TEMPORARY TABLE `temp_t1`(i INT) ENGINE=INNODB;
+SELECT f1();
+SELECT * FROM temp_t1;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Create and drop a temporary table within a function.
+--echo #
+
+delimiter |;
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+ DROP TEMPORARY TABLE IF EXISTS temp_t1;
+ CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+ INSERT INTO temp_t1 VALUES(1);
+ DROP TABLE temp_t1;
+ RETURN 0;
+END|
+
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+ DROP TEMPORARY TABLE IF EXISTS temp_t1;
+ CREATE TEMPORARY TABLE temp_t1(i INT) ENGINE=INNODB;
+ INSERT INTO temp_t1 VALUES(1);
+ DROP TEMPORARY TABLE temp_t1;
+ RETURN 0;
+END|
+delimiter ;|
+SELECT f2();
+DROP FUNCTION f2;
+
+--echo #
+--echo # Create a temporary table within a function and select it from another
+--echo # function.
+--echo #
+
+delimiter |;
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+ DROP TEMPORARY TABLE IF EXISTS temp_t1;
+ CREATE TEMPORARY TABLE temp_t1 (i INT) ENGINE=INNODB;
+ INSERT INTO temp_t1 VALUES (1);
+ RETURN f2_1();
+END|
+
+CREATE FUNCTION f2_1() RETURNS INT
+ RETURN (SELECT COUNT(*) FROM temp_t1)|
+delimiter ;|
+
+SELECT f2();
+DROP TEMPORARY TABLE temp_t1;
+DROP FUNCTION f2;
+
+--echo #
+--echo # Create temporary table like base table.
+--echo #
+
+CREATE TABLE t1(i INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES(1);
+CREATE TEMPORARY TABLE temp_t1 LIKE t1;
+SELECT * FROM temp_t1;
+--error ER_NONUNIQ_TABLE
+CREATE TEMPORARY TABLE t1 LIKE t1;
+DROP TABLE temp_t1, t1;
+
+--echo #
+--echo # Create temporary table as base table.
+--echo #
+
+CREATE TABLE t1(i INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES(1);
+CREATE TEMPORARY TABLE temp_t1 AS SELECT * FROM t1;
+SELECT * FROM temp_t1;
+DROP TABLE temp_t1, t1;
+
+--echo #
+--echo # ALTER TABLE RENAME & ENABLE/DISABLE KEYS (shortcuts)
+--echo #
+CREATE TEMPORARY TABLE t1(i INT PRIMARY KEY) ENGINE=MYISAM;
+INSERT INTO t1 VALUES(1);
+SELECT COUNT(*)=1 FROM t1;
+
+ALTER TABLE t1 RENAME t2;
+SELECT COUNT(*)=1 FROM t2;
+ALTER TABLE t2 RENAME t1;
+
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+
+# LOCK TABLES is ignored for temporary tables.
+LOCK TABLES t1 WRITE;
+ALTER TABLE t1 RENAME t2;
+SELECT COUNT(*)=1 FROM t2;
+ALTER TABLE t2 RENAME t1;
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+UNLOCK TABLES;
+
+LOCK TABLES t1 READ;
+ALTER TABLE t1 RENAME t2;
+SELECT COUNT(*)=1 FROM t2;
+ALTER TABLE t2 RENAME t1;
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+UNLOCK TABLES;
+
+FLUSH TABLES WITH READ LOCK;
+ALTER TABLE t1 RENAME t2;
+SELECT COUNT(*)=1 FROM t2;
+ALTER TABLE t2 RENAME t1;
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+UNLOCK TABLES;
+
+ALTER TABLE t1 RENAME t2, LOCK SHARED;
+ALTER TABLE t2 RENAME t1, LOCK EXCLUSIVE;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10792: Assertion `thd->mdl_context.is_lock_owner
+--echo # (MDL_key::TABLE, table->db, table->table_name, MDL_SHARED)'
+--echo # failed in mysql_rm_table_no_locks
+--echo #
+CREATE TEMPORARY TABLE t1 (i INT);
+--error ER_BAD_TABLE_ERROR
+DROP TABLE nonexisting_table, t1;
+
+--echo # Cleanup
+DROP DATABASE temp_db;
+USE test;
+
+
+--echo #
+--echo # MDEV-17167 - InnoDB: Failing assertion: table->get_ref_count() == 0
+--echo # upon truncating a temporary table
+--echo #
+CREATE TEMPORARY TABLE t1(a INT) ENGINE=InnoDB;
+SELECT * FROM t1 AS t1a1, t1 AS t2a2;
+TRUNCATE TABLE t1;
+
+LOCK TABLES t1 WRITE;
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+UNLOCK TABLES;
+
+LOCK TABLES t1 AS t1a1 WRITE, t1 AS t1a2 WRITE;
+TRUNCATE TABLE t1;
+SELECT * FROM t1 AS t1a1, t1 AS t1a2;
+UNLOCK TABLES;
+
+CREATE TABLE t2(a INT) ENGINE=InnoDB;
+LOCK TABLES t2 WRITE;
+TRUNCATE TABLE t1;
+UNLOCK TABLES;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-19449 1030: Got error 168 "Unknown (generic) error from engine"
+--echo # for valid TRUNCATE (temporary) TABLE
+--echo #
+
+CREATE TEMPORARY TABLE t1 (col1 BIGINT) ENGINE = InnoDB;
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 (no_such_col) SELECT * FROM t1;
+TRUNCATE TABLE t1;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 CHANGE no_such_col1 col1 BIGINT NULL;
+# This would wrongly try to re-truncate the old copy of the table that
+# was not dropped during the first TRUNCATE due to extra table handles.
+TRUNCATE TABLE t1;
+DROP TEMPORARY TABLE t1;
+
+--echo #
+--echo # MDEV-21695 Server crashes in TABLE::evaluate_update_default_function upon UPDATE on temporary table
+--echo #
+
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
+CREATE TEMPORARY TABLE t1 (a DATETIME ON UPDATE CURRENT_TIMESTAMP);
+ALTER TABLE t1 ADD b INT;
+INSERT INTO t1 (b) VALUES (1),(2);
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 CHANGE COLUMN x xx INT;
+UPDATE t1 SET b = 3;
+SELECT * FROM t1;
+DROP TEMPORARY TABLE t1;
+
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+#
+# DROP TEMPORARY TABLE fails in the middle
+#
+delimiter $$;
+create function f1() returns int
+begin
+ drop temporary table t1, t2;
+ return 1;
+end;
+$$
+delimiter ;$$
+
+create temporary table t1 (a int);
+create temporary table t2 (a int);
+insert t1 values (2);
+insert t2 values (3);
+--error ER_CANT_REOPEN_TABLE
+select a,f1() from t1;
+drop function f1;
+drop temporary table t1;
+--error ER_BAD_TABLE_ERROR
+drop temporary table t2;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
+
+--echo #
+--echo # Record that temporary table locks are always WRITE locks
+--echo #
+
+CREATE TEMPORARY TABLE t1 (a INT);
+LOCK TABLE t1 READ;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t1;
+UNLOCK TABLES;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.6 tests
+--echo #