Bug #13231 mysqltest: fails to dectect when mysql_next_result fails

 - Packets out of order when reading cached data stored by a normal select from a ps or vice versa.
 - Add pkt_nr to query cache flags 


mysql-test/r/query_cache.result:
  Update test results
  Add the resultsets that wasn't there before.
mysql-test/t/query_cache.test:
  Improve test to call queries from both a regular statment and a statement inside a stored procedure.
sql/mysql_priv.h:
  Add pkt_nr to query cache flags
sql/sql_cache.cc:
  Set pkt_nr in flags before retrieving/storing a query in the cache

4 files changed, 206 insertions, 15 deletions

diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 0efd5ac1566..f2c3e617144 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1057,42 +1057,64 @@ create table t1 (s1 int)//
 create procedure f1 () begin
 select sql_cache * from t1;
 select sql_cache * from t1;
+select sql_cache * from t1;
+end;//
+create procedure f2 () begin
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+end;//
+create procedure f3 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
+create procedure f4 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1 where s1=1;
 end;//
 call f1();
 s1
+s1
+s1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	1
+Qcache_queries_in_cache	3
 show status like "Qcache_inserts";
 Variable_name	Value
-Qcache_inserts	1
+Qcache_inserts	3
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	1
+Qcache_hits	0
 call f1();
 s1
+s1
+s1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	1
+Qcache_queries_in_cache	3
 show status like "Qcache_inserts";
 Variable_name	Value
-Qcache_inserts	1
+Qcache_inserts	3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	3
 call f1();
 s1
+s1
+s1
 select sql_cache * from t1;
 s1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	2
+Qcache_queries_in_cache	4
 show status like "Qcache_inserts";
 Variable_name	Value
-Qcache_inserts	2
+Qcache_inserts	4
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	5
+Qcache_hits	6
 insert into t1 values (1);
 select sql_cache * from t1;
 s1
@@ -1102,28 +1124,150 @@ Variable_name	Value
 Qcache_queries_in_cache	1
 show status like "Qcache_inserts";
 Variable_name	Value
-Qcache_inserts	3
+Qcache_inserts	5
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	5
+Qcache_hits	6
 call f1();
 s1
 1
+s1
+1
+s1
+1
 call f1();
 s1
 1
+s1
+1
+s1
+1
 select sql_cache * from t1;
 s1
 1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	2
+Qcache_queries_in_cache	4
 show status like "Qcache_inserts";
 Variable_name	Value
-Qcache_inserts	4
+Qcache_inserts	8
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	9
+Qcache_hits	10
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+s1
+1
+select sql_cache * from t1 where s1=1;
+s1
+1
+call f1();
+s1
+1
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+select sql_cache * from t1 where s1=1;
+s1
+1
+insert into t1 values (2);
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
+select sql_cache * from t1 where s1=1;
+s1
+1
+select sql_cache * from t1;
+s1
+1
+2
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
+call f3();
+s1
+1
+2
+s1
+1
+call f3();
+s1
+1
+2
+s1
+1
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
 drop procedure f1;
+drop procedure f2;
+drop procedure f3;
+drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 822c27fe40d..c21d6c46b6c 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -776,6 +776,7 @@ delimiter ;//
 
 #
 # query in QC from normal execution and SP (BUG#6897)
+# improved to also test BUG#3583 and BUG#12990
 #
 flush query cache;
 reset query cache;
@@ -785,6 +786,22 @@ create table t1 (s1 int)//
 create procedure f1 () begin
 select sql_cache * from t1;
 select sql_cache * from t1;
+select sql_cache * from t1;
+end;//
+create procedure f2 () begin
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+end;//
+create procedure f3 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
+create procedure f4 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1 where s1=1;
 end;//
 delimiter ;//
 call f1();
@@ -811,7 +828,32 @@ select sql_cache * from t1;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_inserts";
 show status like "Qcache_hits";
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+call f1();
+call f2();
+call f3();
+call f4();
+call f4();
+call f3();
+call f2();
+select sql_cache * from t1 where s1=1;
+insert into t1 values (2);
+call f1();
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+call f1();
+call f3();
+call f3();
+call f1();
+
 drop procedure f1;
+drop procedure f2;
+drop procedure f3;
+drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 2d45360ab64..819c00abebb 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -537,6 +537,7 @@ struct Query_cache_query_flags
   unsigned int client_long_flag:1;
   unsigned int client_protocol_41:1;
   unsigned int more_results_exists:1;
+  unsigned int pkt_nr;
   uint character_set_client_num;
   uint character_set_results_num;
   uint collation_connection_num;
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 04663c5b096..62daaae9ae6 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -801,6 +801,7 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
                                    CLIENT_PROTOCOL_41);
     flags.more_results_exists= test(thd->server_status &
                                     SERVER_MORE_RESULTS_EXISTS);
+    flags.pkt_nr= net->pkt_nr;
     flags.character_set_client_num=
       thd->variables.character_set_client->number;
     flags.character_set_results_num=
@@ -814,12 +815,13 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
     flags.sql_mode= thd->variables.sql_mode;
     flags.max_sort_length= thd->variables.max_sort_length;
     flags.group_concat_max_len= thd->variables.group_concat_max_len;
-    DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, \
+    DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, pkt_nr: %d, \
 CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \
 sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
                           (int)flags.client_long_flag,
                           (int)flags.client_protocol_41,
                           (int)flags.more_results_exists,
+                          flags.pkt_nr,
                           flags.character_set_client_num,
                           flags.character_set_results_num,
                           flags.collation_connection_num,
@@ -1019,6 +1021,7 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
                                  CLIENT_PROTOCOL_41);
   flags.more_results_exists= test(thd->server_status &
                                   SERVER_MORE_RESULTS_EXISTS);
+  flags.pkt_nr= thd->net.pkt_nr;
   flags.character_set_client_num= thd->variables.character_set_client->number;
   flags.character_set_results_num=
     (thd->variables.character_set_results ?
@@ -1030,12 +1033,13 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
   flags.sql_mode= thd->variables.sql_mode;
   flags.max_sort_length= thd->variables.max_sort_length;
   flags.group_concat_max_len= thd->variables.group_concat_max_len;
-  DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, \
+  DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, pkt_nr: %d, \
 CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \
 sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
                           (int)flags.client_long_flag,
                           (int)flags.client_protocol_41,
                           (int)flags.more_results_exists,
+                          flags.pkt_nr,
                           flags.character_set_client_num,
                           flags.character_set_results_num,
                           flags.collation_connection_num,