From: Date: September 1 2006 2:26am
Subject: bk commit into 5.0 tree (kostja:1.2257) BUG#21959
List-Archive: http://lists.mysql.com/commits/11204
X-Bug: 21959
Message-Id: <20060901002637.CF55B10A8@bodhi.local>

Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja 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-09-01 04:26:30+04:00, kostja@stripped +3 -0
  A fix and a test case for Bug#20045 "cache invalidate crashes when 
  INSERT .. SELECT the query uses a VIEW".
  
  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.
  The fix is to distinguish views from base tables in query cache
  and invalidate them by name.
  
  The following alternative solutions were considered:
  1) Skip views when invalidating tables.
  This would introduce a bug as queries like select * from v1 wouldn't get
  invalidated after after an insert via a view.
  2) Exclude views from query cache altogether. Bad, as we'd like queries 
  that use views (like select * from v1) to be cacheable.
  3) Invalidate only the first table in insert .. select. The fact
  that insert ... select invalidates all used tables is a bug
  and was reported separately (Bug#21959). Besides, fixing only
  insert .. select won't fix the problem as there're also multi-delete
  and multi-update.
  4) Update query cache code to properly handle views. (Implemented in this 
  patch).

  mysql-test/r/query_cache.result@stripped, 2006-09-01 04:26:27+04:00, kostja@stripped +109 -0
    Test results fixed (Bug#20045)

  mysql-test/t/query_cache.test@stripped, 2006-09-01 04:26:27+04:00, kostja@stripped +72 -1
    Add a test case for Bug#20045 "Qcache invalidate crashes when 
    INSERT .. SELECT the query uses a VIEW.

  sql/sql_cache.cc@stripped, 2006-09-01 04:26:27+04:00, kostja@stripped +3 -2
    In case it's a view, invalidate it by table name, not by TABLE object.

# 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:	kostja
# Host:	bodhi.local
# Root:	/opt/local/work/mysql-5.0-20045

--- 1.93/sql/sql_cache.cc	2006-09-01 04:26:37 +04:00
+++ 1.94/sql/sql_cache.cc	2006-09-01 04:26:37 +04:00
@@ -1312,10 +1312,11 @@ void Query_cache::invalidate(THD *thd, T
       (thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN));
     for (; tables_used; tables_used= tables_used->next_local)
     {
-      DBUG_ASSERT(!using_transactions || tables_used->table!=0);
+      DBUG_ASSERT(!using_transactions || tables_used->table ||
+                  tables_used->view);
       if (tables_used->derived)
         continue;
-      if (using_transactions &&
+      if (tables_used->view == NULL && using_transactions &&
          (tables_used->table->file->table_cache_type() ==
           HA_CACHE_TBL_TRANSACT))
         /*

--- 1.72/mysql-test/r/query_cache.result	2006-09-01 04:26:37 +04:00
+++ 1.73/mysql-test/r/query_cache.result	2006-09-01 04:26:37 +04:00
@@ -1252,6 +1252,7 @@ drop procedure f3;
 drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;
+End of 4.1 tests
 SET GLOBAL query_cache_size=102400;
 create table t1(a int);
 insert into t1 values(0), (1), (4), (5);
@@ -1267,4 +1268,112 @@ show status like 'last_query_cost';
 Variable_name	Value
 Last_query_cost	0.000000
 drop table t1;
+flush status;
+drop table if exists t1, t2, t3;
+drop view if exists v1, v2;
+create table t1(c1 int);
+create table t2(c1 int);
+create table t3(c1 int);
+create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1;
+start transaction;
+insert into t1(c1) select c1 from v1;
+insert into t1(c1) select c1 from v1;
+drop table t1, t2, t3;
+drop view v1;
+create table t1 (i int);
+insert into t1 values (1);
+create view v1 as select * from t1;
+select * from t1;
+i
+1
+select * from v1;
+i
+1
+insert into v1 values (2);
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+i
+1
+2
+select * from v1;
+i
+1
+2
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+create table t2 select * from t1;
+create view v2 as select * from t2;
+select * from t2;
+i
+1
+2
+select * from v2;
+i
+1
+2
+update v1, v2 set v1.i=3, v2.i=4 where v1.i=2 and v1.i=v2.i;
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+i
+1
+3
+select * from t2;
+i
+1
+4
+select * from v1;
+i
+1
+3
+select * from v2;
+i
+1
+4
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+delete v1.*, v2.* from v1, v2 where v1.i=3 and v2.i=4;
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+i
+1
+select * from t2;
+i
+1
+select * from v1;
+i
+1
+select * from v2;
+i
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+i
+1
+select * from v2;
+i
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	2
+insert into t1 (i) values (2);
+select * from v1;
+i
+1
+2
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	2
+drop view v1, v2;
+drop table t1, t2;
+End of 5.0 tests
 SET GLOBAL query_cache_size=0;

--- 1.54/mysql-test/t/query_cache.test	2006-09-01 04:26:37 +04:00
+++ 1.55/mysql-test/t/query_cache.test	2006-09-01 04:26:37 +04:00
@@ -856,7 +856,7 @@ drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;
 
-# End of 4.1 tests
+--echo End of 4.1 tests
 
 #
 # Bug #10303: problem with last_query_cost
@@ -869,4 +869,75 @@ select * from t1 where a > 3;
 select * from t1 where a > 3;
 show status like 'last_query_cost';
 drop table t1;
+
+#
+# Bug#20045 "Qcache invalidate crashes when INSERT .. SELECT the query uses
+#            a VIEW"
+#
+flush status;
+--disable_warnings
+drop table if exists t1, t2, t3;
+drop view if exists v1, v2;
+--enable_warnings
+create table t1(c1 int);
+create table t2(c1 int);
+create table t3(c1 int);
+create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1;
+start transaction;
+insert into t1(c1) select c1 from v1;
+insert into t1(c1) select c1 from v1;
+drop table t1, t2, t3;
+drop view v1;
+#
+# check that query cache invalidation works OK when inserting into a view
+#
+create table t1 (i int);
+insert into t1 values (1);
+create view v1 as select * from t1;
+select * from t1;
+select * from v1;
+insert into v1 values (2);
+show status like 'Qcache_hits';
+select * from t1;
+select * from v1;
+show status like 'Qcache_hits';
+#
+# Check that multi-update and multi-delete using views works with query
+# cache enabled and correctly invalidates data from the cache.
+#
+create table t2 select * from t1;
+create view v2 as select * from t2;
+# Add queries to the query cache
+select * from t2;
+select * from v2;
+update v1, v2 set v1.i=3, v2.i=4 where v1.i=2 and v1.i=v2.i;
+show status like 'Qcache_hits';
+# We must ensure that this invaliates both, queries that mention
+# views and queries that mention base tables.
+select * from t1;
+select * from t2;
+select * from v1;
+select * from v2;
+show status like 'Qcache_hits';
+#
+delete v1.*, v2.* from v1, v2 where v1.i=3 and v2.i=4; 
+show status like 'Qcache_hits';
+select * from t1;
+select * from t2;
+select * from v1;
+select * from v2;
+show status like 'Qcache_hits';
+# Check that queries that use views are cacheable after all
+select * from v1;
+select * from v2;
+show status like 'Qcache_hits';
+# Check that a view is invalidated when its base table is updated
+insert into t1 (i) values (2);
+select * from v1;
+show status like 'Qcache_hits';
+#
+drop view v1, v2;
+drop table t1, t2;
+
+--echo End of 5.0 tests
 SET GLOBAL query_cache_size=0;