merge

4 files changed, 147 insertions, 0 deletions

diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 506a76579ae..8123522cd2c 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -924,6 +924,94 @@ group_concat(a)
 12345678901234567890
 set group_concat_max_len=default;
 drop table t1;
+flush status;
+CREATE TABLE t1 (
+`date` datetime NOT NULL default '0000-00-00 00:00:00',
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('20050326');
+INSERT INTO t1 VALUES ('20050325');
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+COUNT(*)
+0
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '20050327 0:0:0'
+Warning	1292	Truncated incorrect datetime value: '20050327 0:0:0'
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 0:0:0';
+COUNT(*)
+0
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '20050328 0:0:0'
+Warning	1292	Truncated incorrect datetime value: '20050328 0:0:0'
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+COUNT(*)
+0
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '20050327 0:0:0'
+Warning	1292	Truncated incorrect datetime value: '20050327 0:0:0'
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+drop table t1;
+create table t1 (a int);
+insert into t1 values (1);
+reset query cache;
+flush status;
+select * from (select * from t1) a;
+a
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from (select * from t1) a;
+a
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+insert into t1 values (2);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+select * from (select * from t1) a;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+
+drop table t1;
 create table t1 (a int);
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 88891bd3881..3a0ac08b1ba 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -757,6 +757,50 @@ flush query cache;
 drop table t1, t2;
 
 #
+# Query with warning prohibited to query cache (BUG#9414)
+#
+flush status;
+CREATE TABLE t1 (
+  `date` datetime NOT NULL default '0000-00-00 00:00:00',
+  KEY `date` (`date`)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES ('20050326');
+INSERT INTO t1 VALUES ('20050325');
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 0:0:0';
+SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+drop table t1;
+
+#
+# queries with subquery in the FROM clause (BUG#11522)
+#
+create table t1 (a int);
+insert into t1 values (1);
+reset query cache;
+flush status;
+select * from (select * from t1) a;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from (select * from t1) a;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+insert into t1 values (2);
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from (select * from t1) a;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+drop table t1;
+
+#
 # SP cursors and selects with query cache (BUG#9715)
 #
 create table t1 (a int);
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 430e0fbcabf..279a1ccb443 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -2135,6 +2135,13 @@ Query_cache::register_tables_from_list(TABLE_LIST *tables_used,
        tables_used;
        tables_used= tables_used->next_global, n++, block_table++)
   {
+    if (tables_used->derived)
+    {
+      DBUG_PRINT("qcache", ("derived table skipped");
+      n--;
+      block_table--;
+      continue;
+    }
     block_table->n= n;
     if (tables_used->view)
     {
@@ -2778,6 +2785,12 @@ static TABLE_COUNTER_TYPE process_and_count_tables(TABLE_LIST *tables_used,
                             tables_used->table->s->table_name,
                             tables_used->table->s->table_cache_key,
                             tables_used->table->s->db_type));
+      if (table_used->derived)
+      {
+        table_count--;
+        DBUG_PRINT("qcache", ("derived table skipped"));
+        continue;
+      }
       *tables_type|= tables_used->table->file->table_cache_type();
 
       /*
diff --git a/sql/sql_error.cc b/sql/sql_error.cc
index 8a12339ccee..2e262569386 100644
--- a/sql/sql_error.cc
+++ b/sql/sql_error.cc
@@ -146,6 +146,8 @@ MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level,
   {
     DBUG_RETURN(NULL);
   }
+  query_cache_abort(&thd->net);
+
 
   if (thd->warn_list.elements < thd->variables.max_error_count)
   {