bzr commit into mysql-5.1-bugteam branch (davi:2739) Bug#41348

[Davi Arnaut]

# At a local mysql-5.1-bugteam repository of davi

 2739 Davi Arnaut	2008-12-16
      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 must always be a write lock.
      
      The solution is to ensure that the lock type of temporary
      tables is never overwritten and that its always a write lock.
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
    Don't update lock type if temporary table.
=== 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-16 16:33:43 +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-16 16:33:43 +0000
@@ -194,4 +194,18 @@ 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 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();
+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-16 16:33:43 +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-16 16:33:43 +0000
@@ -204,4 +204,32 @@ 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 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();
+
+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-16 16:33:43 +0000
@@ -1101,6 +1101,7 @@ static void mark_temp_tables_as_free_for
   {
     if ((table->query_id == thd->query_id) && ! table->open_by_handler)
     {
+      DBUG_ASSERT(table->reginfo.lock_type == TL_WRITE);
       table->query_id= 0;
       table->file->ha_reset();
       /*
@@ -4674,14 +4675,15 @@ int open_tables(THD *thd, TABLE_LIST **s
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
     }
 
-    if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
+    if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables &&
+        tables->table->s->tmp_table == NO_TMP_TABLE)
     {
       if (tables->lock_type == TL_WRITE_DEFAULT)
         tables->table->reginfo.lock_type= thd->update_lock_default;
       else if (tables->lock_type == TL_READ_DEFAULT)
         tables->table->reginfo.lock_type=
           read_lock_type_for_table(thd, tables->table);
-      else if (tables->table->s->tmp_table == NO_TMP_TABLE)
+      else
         tables->table->reginfo.lock_type= tables->lock_type;
     }
     tables->table->grant= tables->grant;