fixed MySQL bug#53775:

Now partition engine adds underlying tables to the QC and ask underlying tables engine permittion to cache the query and return result of the query.

Incorrect QC cleanup in case of table registration failure fixe.

Unified interface for myisammrg & partitioned engnes for QC.

6 files changed, 467 insertions, 9 deletions

diff --git a/mysql-test/include/query_cache_partitions.inc b/mysql-test/include/query_cache_partitions.inc
new file mode 100644
index 00000000000..e5bb7406c10
--- /dev/null
+++ b/mysql-test/include/query_cache_partitions.inc
@@ -0,0 +1,126 @@
+# include/query_cache_partitions.inc
+#
+# The variables
+#     $engine_type       -- storage engine to be tested
+# have to be set before sourcing this script.
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
+
+# Initialise
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+set @save_query_cache_size = @@global.query_cache_size;
+
+--echo # Test that partitions works with query cache
+
+flush query cache;
+
+SET GLOBAL query_cache_size=1024*1024*512;
+  CREATE TABLE `t1` (
+    `id` int(11) NOT NULL ,
+    `created_at` datetime NOT NULL,
+    `cool` tinyint default 0
+  );
+
+  ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+    PARTITION month_2010_4 VALUES LESS THAN (734258),
+    PARTITION month_2010_5 VALUES LESS THAN (734289),
+    PARTITION month_max VALUES LESS THAN MAXVALUE
+  );
+
+show create table t1;
+
+INSERT INTO t1 VALUES (1, now(), 0);
+
+flush status;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+drop table t1;
+
+--echo # Test that sub-partitions works with query cache
+
+flush query cache;
+
+SET GLOBAL query_cache_size=1024*1024*512;
+  CREATE TABLE `t1` (
+    `id` int(11) NOT NULL ,
+    `created_at` datetime NOT NULL,
+    `cool` tinyint default 0
+  )
+    PARTITION BY RANGE (TO_DAYS(created_at))
+    subpartition by hash(cool) subpartitions 3 (
+    PARTITION month_2010_4 VALUES LESS THAN (734258),
+    PARTITION month_2010_5 VALUES LESS THAN (734289),
+    PARTITION month_max VALUES LESS THAN MAXVALUE
+  );
+
+show create table t1;
+
+INSERT INTO t1 VALUES (1, now(), 0);
+
+flush status;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+drop table t1;
+
+--echo #
+--echo # MySQL bug#53775 Query on partitioned table returns cached result
+--echo # from previous transaction
+--echo #
+
+flush query cache;
+flush status;
+
+SET GLOBAL query_cache_size=1024*1024*512;
+  CREATE TABLE `t1` (
+    `id` int(11) NOT NULL ,
+    `created_at` datetime NOT NULL,
+    `cool` tinyint default 0
+  );
+
+  ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+    PARTITION month_2010_4 VALUES LESS THAN (734258),
+    PARTITION month_2010_5 VALUES LESS THAN (734289),
+    PARTITION month_max VALUES LESS THAN MAXVALUE
+  );
+
+INSERT INTO t1 VALUES (1, now(), 0);
+
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+drop table t1;
+
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result
index ac2da9bb78a..cd579d00952 100644
--- a/mysql-test/r/partition_cache.result
+++ b/mysql-test/r/partition_cache.result
@@ -27,7 +27,7 @@ a
 3
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 drop table t1;
 commit;
 create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3;
@@ -50,7 +50,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -69,7 +69,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	6
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -93,14 +93,14 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 commit;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 drop table t3,t2,t1;
 CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3;
 select count(*) from t1;
@@ -164,7 +164,7 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 connection connection1
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
@@ -197,9 +197,9 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 set @@global.query_cache_size = @save_query_cache_size;
 drop table t2;
diff --git a/mysql-test/r/partition_cache_innodb.result b/mysql-test/r/partition_cache_innodb.result
new file mode 100644
index 00000000000..0d0abbb096c
--- /dev/null
+++ b/mysql-test/r/partition_cache_innodb.result
@@ -0,0 +1,151 @@
+SET SESSION STORAGE_ENGINE = innodb;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/r/partition_cache_myisam.result b/mysql-test/r/partition_cache_myisam.result
new file mode 100644
index 00000000000..0b617c03590
--- /dev/null
+++ b/mysql-test/r/partition_cache_myisam.result
@@ -0,0 +1,153 @@
+SET SESSION STORAGE_ENGINE = myisam;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/t/partition_cache_innodb.test b/mysql-test/t/partition_cache_innodb.test
new file mode 100644
index 00000000000..dbcfea3088c
--- /dev/null
+++ b/mysql-test/t/partition_cache_innodb.test
@@ -0,0 +1,14 @@
+# t/cache_innodb.test
+#
+# Last update:
+# 2006-07-26 ML test refactored (MySQL 5.1)
+#               main code t/innodb_cache.test --> include/query_cache.inc
+#               new wrapper t/cache_innodb.test
+#
+
+--source include/have_query_cache.inc
+--source include/have_innodb.inc
+--source include/have_partition.inc
+let $engine_type= innodb;
+
+--source include/query_cache_partitions.inc
diff --git a/mysql-test/t/partition_cache_myisam.test b/mysql-test/t/partition_cache_myisam.test
new file mode 100644
index 00000000000..5347225f6da
--- /dev/null
+++ b/mysql-test/t/partition_cache_myisam.test
@@ -0,0 +1,14 @@
+# t/cache_innodb.test
+#
+# Last update:
+# 2006-07-26 ML test refactored (MySQL 5.1)
+#               main code t/innodb_cache.test --> include/query_cache.inc
+#               new wrapper t/cache_innodb.test
+#
+
+--source include/have_query_cache.inc
+
+--source include/have_partition.inc
+let $engine_type= myisam;
+
+--source include/query_cache_partitions.inc