# At a local mysql-5.1-bugteam repository of davi
2739 Davi Arnaut 2008-12-17
Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites
locking type of temp table
The problem is that INSERT INTO .. SELECT FROM .. and CREATE
TABLE .. SELECT FROM a temporary table could inadvertently
overwrite the locking type of the temporary table. The lock
type of temporary tables should be a write lock by default.
The solution is to reset the lock type of temporary tables
back to its default value after they are used in a statement.
modified:
mysql-test/r/innodb_mysql.result
mysql-test/r/temp_table.result
mysql-test/t/innodb_mysql.test
mysql-test/t/temp_table.test
sql/sql_base.cc
per-file messages:
mysql-test/r/innodb_mysql.result
Add test case result for Bug#41348
mysql-test/r/temp_table.result
Add test case result for Bug#41348
mysql-test/t/innodb_mysql.test
Add test case for Bug#41348
mysql-test/t/temp_table.test
Add test case for Bug#41348
sql/sql_base.cc
Reset temporary table lock type.
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2008-12-08 10:23:33 +0000
+++ b/mysql-test/r/innodb_mysql.result 2008-12-17 11:38:12 +0000
@@ -1767,4 +1767,25 @@ ref NULL
rows 6
Extra Using where; Using index
DROP TABLE foo, bar, foo2;
+DROP TABLE IF EXISTS t1,t3,t2;
+DROP FUNCTION IF EXISTS f1;
+CREATE FUNCTION f1() RETURNS VARCHAR(250)
+BEGIN
+return 'hhhhhhh' ;
+END|
+CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
+BEGIN WORK;
+CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
+CREATE TEMPORARY TABLE t3 LIKE t2;
+INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
+SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
+PREPARE stmt1 FROM @stmt;
+SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
+PREPARE stmt3 FROM @stmt;
+EXECUTE stmt1;
+COMMIT;
+DEALLOCATE PREPARE stmt1;
+DEALLOCATE PREPARE stmt3;
+DROP TABLE t1,t3,t2;
+DROP FUNCTION f1;
End of 5.1 tests
=== modified file 'mysql-test/r/temp_table.result'
--- a/mysql-test/r/temp_table.result 2008-03-28 15:16:52 +0000
+++ b/mysql-test/r/temp_table.result 2008-12-17 11:38:12 +0000
@@ -194,4 +194,20 @@ DELETE FROM t1;
SELECT * FROM t1;
a b
DROP TABLE t1;
+DROP TABLE IF EXISTS t1,t2;
+DROP FUNCTION IF EXISTS f1;
+CREATE TEMPORARY TABLE t1 (a INT);
+CREATE TEMPORARY TABLE t2 LIKE t1;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+return 1;
+END|
+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;
End of 5.1 tests
=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test 2008-12-08 10:23:33 +0000
+++ b/mysql-test/t/innodb_mysql.test 2008-12-17 11:38:12 +0000
@@ -91,4 +91,45 @@ INSERT INTO foo2 SELECT * FROM foo;
DROP TABLE foo, bar, foo2;
+#
+# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t3,t2;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+DELIMITER |;
+CREATE FUNCTION f1() RETURNS VARCHAR(250)
+ BEGIN
+ return 'hhhhhhh' ;
+ END|
+DELIMITER ;|
+
+CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
+
+BEGIN WORK;
+
+CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
+CREATE TEMPORARY TABLE t3 LIKE t2;
+
+INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
+
+SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
+PREPARE stmt1 FROM @stmt;
+
+SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
+PREPARE stmt3 FROM @stmt;
+
+EXECUTE stmt1;
+
+COMMIT;
+
+DEALLOCATE PREPARE stmt1;
+DEALLOCATE PREPARE stmt3;
+
+DROP TABLE t1,t3,t2;
+DROP FUNCTION f1;
+
--echo End of 5.1 tests
=== modified file 'mysql-test/t/temp_table.test'
--- a/mysql-test/t/temp_table.test 2008-03-28 15:16:52 +0000
+++ b/mysql-test/t/temp_table.test 2008-12-17 11:38:12 +0000
@@ -204,4 +204,35 @@ 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 End of 5.1 tests
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2008-11-27 15:03:13 +0000
+++ b/sql/sql_base.cc 2008-12-17 11:38:12 +0000
@@ -1111,6 +1111,27 @@ static void mark_temp_tables_as_free_for
*/
if (table->child_l || table->parent)
detach_merge_children(table, TRUE);
+ /*
+ Reset temporary table lock type to it's default value (TL_WRITE).
+
+ Statements such as INSERT INTO .. SELECT FROM tmp, CREATE TABLE
+ .. SELECT FROM tmp and UPDATE may under some circumstances modify
+ the lock type of the tables participating in the statement. This
+ isn't a problem for non-temporary tables since their lock type is
+ reset at every open, but the same does not occur for temporary
+ tables for historical reasons.
+
+ Furthermore, the lock type of temporary tables is not really that
+ important because they can only be used by one query at a time and
+ not even twice in a query -- a temporary table is represented by
+ only one TABLE object. Nonetheless, it's safer from a maintenance
+ point of view to reset the lock type of this singleton TABLE object
+ as to not cause problems when the table is reused.
+
+ Even under LOCK TABLES mode its okay to reset the lock type as
+ LOCK TABLES is allowed (but ignored) for a temporary table.
+ */
+ table->reginfo.lock_type= TL_WRITE;
}
}
}
