bk commit into 5.0 tree (evgen:1.2304) BUG#20045

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-09 18:05:40+03:00, evgen@stripped +5 -0
  Bug#20045: Server crash on INSERT ... SELECT with a view 
  
  The regression is caused by the fix for bug 14767. When INSERT ... SELECT
  used a view in the SELECT list that was not inlined, and there was an 
  active transaction, the server could crash in Query_cache::invalidate.
  
  On INSERT ... SELECT only the table being inserted into is invalidated.
  Thus views that can't be inlined are skipped from invalidation.

  mysql-test/r/ps.result@stripped, 2006-11-09 18:03:21+03:00, evgen@stripped +16 -0
    Added a test case for bug#20045: Server crash on INSERT ... SELECT with a view

  mysql-test/r/query_cache.result@stripped, 2006-11-09 17:21:56+03:00, evgen@stripped
+40 -0
    Added a test case for bug#21959: INSERT ... SELECT wrongly invalidates the table being
selected from

  mysql-test/t/ps.test@stripped, 2006-11-09 18:03:23+03:00, evgen@stripped +21 -0
    Added a test case for bug#20045: Server crash on INSERT ... SELECT with a view

  mysql-test/t/query_cache.test@stripped, 2006-11-09 17:21:25+03:00, evgen@stripped +22
-0
    Added a test case for bug#21959: INSERT ... SELECT wrongly invalidates the table being
selected from

  sql/sql_parse.cc@stripped, 2006-11-09 17:26:23+03:00, evgen@stripped +4 -0
    Bug#20045: Server crash on INSERT ... SELECT with a view
    On INSERT ... SELECT only the table being inserted into is invalidated.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/20045-bug-5.0-opt-mysql

--- 1.582/sql/sql_parse.cc	2006-11-09 18:05:43 +03:00
+++ 1.583/sql/sql_parse.cc	2006-11-09 18:05:43 +03:00
@@ -3421,8 +3421,12 @@
         if (first_table->lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
             thd->lock)
         {
+          /* INSERT ... SELECT should invalidate only the very first table */
+          TABLE_LIST *save_table= first_table->next_local;
+          first_table->next_local= 0;
           mysql_unlock_tables(thd, thd->lock);
           query_cache_invalidate3(thd, first_table, 1);
+          first_table->next_local= save_table;
           thd->lock=0;
         }
         delete result;

--- 1.75/mysql-test/r/query_cache.result	2006-11-09 18:05:43 +03:00
+++ 1.76/mysql-test/r/query_cache.result	2006-11-09 18:05:43 +03:00
@@ -1274,3 +1274,43 @@
 Last_query_cost	0.000000
 drop table t1;
 SET GLOBAL query_cache_size=0;
+set global query_cache_size=1024*1024;
+flush status;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3);
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	1
+create table t2 like t1;
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	2
+insert into t2 select * from t1;
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	2
+drop table t1, t2;
+set global query_cache_size=0;

--- 1.54/mysql-test/t/query_cache.test	2006-11-09 18:05:43 +03:00
+++ 1.55/mysql-test/t/query_cache.test	2006-11-09 18:05:43 +03:00
@@ -870,3 +870,25 @@
 show status like 'last_query_cost';
 drop table t1;
 SET GLOBAL query_cache_size=0;
+
+#
+# Bug #20959: INSERT ... SELECT wrongly invalidates the table being
+#             selected from
+#
+set global query_cache_size=1024*1024;
+flush status;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3);
+select * from t1;
+show status like 'Qcache_hits';
+select * from t1;
+show status like 'Qcache_hits';
+create table t2 like t1;
+select * from t1;
+show status like 'Qcache_hits';
+insert into t2 select * from t1;
+select * from t1;
+show status like 'Qcache_hits';
+drop table t1, t2;
+set global query_cache_size=0;
+

--- 1.76/mysql-test/r/ps.result	2006-11-09 18:05:43 +03:00
+++ 1.77/mysql-test/r/ps.result	2006-11-09 18:05:43 +03:00
@@ -1379,4 +1379,20 @@
 1
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1, t2;
+CREATE TABLE `t1`(`c1` int) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t2`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t3`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE VIEW `v1` AS select `t3`.`c1` AS `c1` FROM `t3`,`t2` WHERE  `t3`.`c1` =
+`t2`.`c1`;
+CREATE VIEW `v2` AS select * from t2;
+SET @sqlstr="INSERT INTO t1(`c1`) SELECT `c1` FROM v1";
+PREPARE stmt FROM @sqlstr;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET @sqlstr="INSERT INTO v2(`c1`) SELECT `c1` FROM v1";
+PREPARE stmt FROM @sqlstr;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3;
 End of 5.0 tests.

--- 1.72/mysql-test/t/ps.test	2006-11-09 18:05:43 +03:00
+++ 1.73/mysql-test/t/ps.test	2006-11-09 18:05:43 +03:00
@@ -1437,4 +1437,25 @@
 DROP TABLE t1, t2;
 
 
+#
+# Bug#20045: Server crash on INSERT ... SELECT with a view 
+#
+CREATE TABLE `t1`(`c1` int) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t2`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t3`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE VIEW `v1` AS select `t3`.`c1` AS `c1` FROM `t3`,`t2` WHERE  `t3`.`c1` =
+`t2`.`c1`;
+CREATE VIEW `v2` AS select * from t2;
+START TRANSACTION;
+SET @sqlstr="INSERT INTO t1(`c1`) SELECT `c1` FROM v1";
+PREPARE stmt FROM @sqlstr;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET @sqlstr="INSERT INTO v2(`c1`) SELECT `c1` FROM v1";
+PREPARE stmt FROM @sqlstr;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+COMMIT;
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3;
 --echo End of 5.0 tests.