From: Date: June 23 2007 7:16pm
Subject: bk commit into 5.1 tree (guilhem:1.2527) BUG#29318
List-Archive: http://lists.mysql.com/commits/29451
X-Bug: 29318
Message-Id: <20070623171657.705A125000@gbichot3.local>

Below is the list of changes that have just been committed into a local
5.1 repository of guilhem. When guilhem 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, 2007-06-23 19:16:51+02:00, guilhem@stripped +4 -0
  Fix for BUG#29318 "Statements prepared with PREPARE and with one
  parameter don't use query cache"
  Thanks to the fix of BUG#26842, statements prepared with SQL PREPARE
  and having parameters can now use the query cache.

  mysql-test/include/query_cache_sql_prepare.inc@stripped, 2007-06-23 19:16:48+02:00, guilhem@stripped +10 -2
    now, statements prepared with SQL PREPARE use the query cache even
    when they have parameters.

  mysql-test/r/query_cache_ps_no_prot.result@stripped, 2007-06-23 19:16:48+02:00, guilhem@stripped +47 -34
    updated result: we see caching happened.

  mysql-test/r/query_cache_ps_ps_prot.result@stripped, 2007-06-23 19:16:48+02:00, guilhem@stripped +47 -34
    updated result: we see caching happened

  sql/sql_prepare.cc@stripped, 2007-06-23 19:16:48+02:00, guilhem@stripped +0 -12
    query expansion does not insert user variables' references anymore,
    it now inserts parameters' values (BUG#26842's fix did this);
    so we can use the query cache.

# 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:	guilhem
# Host:	gbichot3.local
# Root:	/home/mysql_src/mysql-5.1-runtime

--- 1.3/mysql-test/r/query_cache_ps_no_prot.result	2007-05-24 22:13:47 +02:00
+++ 1.4/mysql-test/r/query_cache_ps_no_prot.result	2007-06-23 19:16:48 +02:00
@@ -144,7 +144,7 @@
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	15
 ---- switch to connection con1 ----
 set @a=1;
 prepare stmt4 from "select * from t1 where c1=?";
@@ -153,50 +153,63 @@
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
+prepare stmt4 from "select @a from t1 where c1=?";
+execute stmt4 using @a;
+@a
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	16
+execute stmt4 using @a;
+@a
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	16
 ---- switch to connection default ----
 prepare stmt1 from "select * from t1 where c1=10";
 set global query_cache_size=0;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 ---- switch to connection default ----
 set global query_cache_size=100000;
 execute stmt1;
@@ -204,80 +217,80 @@
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	15
+Qcache_hits	17
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	16
+Qcache_hits	18
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	18
+Qcache_hits	20
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 ---- switch to connection default ----
 set global query_cache_size=0;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 ---- switch to connection default ----
 set global query_cache_size=0;
 prepare stmt1 from "select * from t1 where c1=10";
@@ -287,75 +300,75 @@
 set global query_cache_size=100000;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 ---- switch to connection con1 ----
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 ---- switch to connection default ----
 set global query_cache_size=0;
 prepare stmt1 from "select * from t1 where c1=?";
 set global query_cache_size=100000;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 set @a=1;
 execute stmt1 using @a;
 c1
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 set @a=100;
 execute stmt1 using @a;
 c1
 100
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 set @a=10;
 execute stmt1 using @a;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	19
+Qcache_hits	21
 drop table t1;
 ---- disconnect connection con1 ----
 set @@global.query_cache_size=@initial_query_cache_size;

--- 1.5/mysql-test/include/query_cache_sql_prepare.inc	2007-05-24 22:13:47 +02:00
+++ 1.6/mysql-test/include/query_cache_sql_prepare.inc	2007-06-23 19:16:48 +02:00
@@ -33,7 +33,7 @@
 create table t1(c1 int);
 insert into t1 values(1),(10),(100);
 
-# Prepared statements has no parameters, query caching should happen
+# First, prepared statements with no parameters
 prepare stmt1 from "select * from t1 where c1=10";
 show status like 'Qcache_hits';
 execute stmt1;
@@ -113,7 +113,9 @@
 --echo ---- switch to connection default ----
 connection default;
 
-# Prepared statement has parameters, query caching should not happen
+# Query caching also works when statement has parameters
+# (BUG#29318 Statements prepared with PREPARE and with one parameter don't use
+# query cache)
 prepare stmt1 from "select * from t1 where c1=?";
 show status like 'Qcache_hits';
 set @a=1;
@@ -125,6 +127,12 @@
 connection con1;
 set @a=1;
 prepare stmt4 from "select * from t1 where c1=?";
+execute stmt4 using @a;
+show status like 'Qcache_hits';
+# verify that presence of user variables forbids caching
+prepare stmt4 from "select @a from t1 where c1=?";
+execute stmt4 using @a;
+show status like 'Qcache_hits';
 execute stmt4 using @a;
 show status like 'Qcache_hits';
 --echo ---- switch to connection default ----

--- 1.1/mysql-test/r/query_cache_ps_ps_prot.result	2007-05-24 22:13:47 +02:00
+++ 1.2/mysql-test/r/query_cache_ps_ps_prot.result	2007-06-23 19:16:48 +02:00
@@ -144,7 +144,7 @@
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	13
 ---- switch to connection con1 ----
 set @a=1;
 prepare stmt4 from "select * from t1 where c1=?";
@@ -153,50 +153,63 @@
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
+prepare stmt4 from "select @a from t1 where c1=?";
+execute stmt4 using @a;
+@a
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	14
+execute stmt4 using @a;
+@a
+1
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	14
 ---- switch to connection default ----
 prepare stmt1 from "select * from t1 where c1=10";
 set global query_cache_size=0;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 ---- switch to connection default ----
 set global query_cache_size=100000;
 execute stmt1;
@@ -204,80 +217,80 @@
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	13
+Qcache_hits	15
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	14
+Qcache_hits	16
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	15
+Qcache_hits	17
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	16
+Qcache_hits	18
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 ---- switch to connection default ----
 set global query_cache_size=0;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 ---- switch to connection con1 ----
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 ---- switch to connection default ----
 set global query_cache_size=0;
 prepare stmt1 from "select * from t1 where c1=10";
@@ -287,75 +300,75 @@
 set global query_cache_size=100000;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 ---- switch to connection con1 ----
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 execute stmt3;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 ---- switch to connection default ----
 set global query_cache_size=0;
 prepare stmt1 from "select * from t1 where c1=?";
 set global query_cache_size=100000;
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 set @a=1;
 execute stmt1 using @a;
 c1
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 set @a=100;
 execute stmt1 using @a;
 c1
 100
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 set @a=10;
 execute stmt1 using @a;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	17
+Qcache_hits	19
 drop table t1;
 ---- disconnect connection con1 ----
 set @@global.query_cache_size=@initial_query_cache_size;

--- 1.225/sql/sql_prepare.cc	2007-06-22 15:39:30 +02:00
+++ 1.226/sql/sql_prepare.cc	2007-06-23 19:16:48 +02:00
@@ -2922,18 +2922,6 @@
   thd->restore_backup_statement(this, &stmt_backup);
   thd->stmt_arena= old_stmt_arena;
 
-  if ((protocol->type() == Protocol::PROTOCOL_TEXT) && (param_count > 0))
-  {
-    /*
-      This is a mysql_sql_stmt_prepare(); query expansion will insert user
-      variable references, and user variables are uncacheable, thus we have to
-      mark this statement as uncacheable.
-      This has to be done before setup_set_params(), as it may make expansion
-      unneeded.
-    */
-    lex->safe_to_cache_query= FALSE;
-  }
-
   if (error == 0)
   {
     setup_set_params();