From: Date: December 17 2008 12:38pm
Subject: bzr commit into mysql-5.1-bugteam branch (davi:2739) Bug#41348
List-Archive: http://lists.mysql.com/commits/61853
X-Bug: 41348
Message-Id: <20081217113823.A6ADAEC101@skynet.ctb.virtua.com.br>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

# 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;
     }
   }
 }