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:
  now, statements prepared with SQL PREPARE use the query cache even
  when they have parameters.
mysql-test/r/query_cache_ps_no_prot.result:
  updated result: we see caching happened.
mysql-test/r/query_cache_ps_ps_prot.result:
  updated result: we see caching happened
sql/sql_prepare.cc:
  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.

4 files changed, 104 insertions, 82 deletions

diff --git a/mysql-test/include/query_cache_sql_prepare.inc b/mysql-test/include/query_cache_sql_prepare.inc
index cdb3bd586e7..cf6d4c26959 100644
--- a/mysql-test/include/query_cache_sql_prepare.inc
+++ b/mysql-test/include/query_cache_sql_prepare.inc
@@ -33,7 +33,7 @@ drop table if exists t1;
 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 @@ show status like 'Qcache_hits';
 --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;
@@ -127,6 +129,12 @@ 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 ----
 connection default;
 
diff --git a/mysql-test/r/query_cache_ps_no_prot.result b/mysql-test/r/query_cache_ps_no_prot.result
index bf162439918..29d16d8a619 100644
--- a/mysql-test/r/query_cache_ps_no_prot.result
+++ b/mysql-test/r/query_cache_ps_no_prot.result
@@ -144,7 +144,7 @@ c1
 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 @@ c1
 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 @@ 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	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 @@ prepare stmt3 from "select * from t1 where c1=10";
 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;
diff --git a/mysql-test/r/query_cache_ps_ps_prot.result b/mysql-test/r/query_cache_ps_ps_prot.result
index 56aeda4a253..ba675d57f50 100644
--- a/mysql-test/r/query_cache_ps_ps_prot.result
+++ b/mysql-test/r/query_cache_ps_ps_prot.result
@@ -144,7 +144,7 @@ c1
 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 @@ c1
 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 @@ 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	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 @@ prepare stmt3 from "select * from t1 where c1=10";
 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;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index d1d70089584..6994ff99422 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2922,18 +2922,6 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len)
   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();