From a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Fri, 9 Mar 2018 14:05:35 +0200 Subject: Create 'main' test directory and move 't' and 'r' there --- mysql-test/main/partition_cache_myisam.result | 156 ++++++++++++++++++++++++++ 1 file changed, 156 insertions(+) create mode 100644 mysql-test/main/partition_cache_myisam.result (limited to 'mysql-test/main/partition_cache_myisam.result') diff --git a/mysql-test/main/partition_cache_myisam.result b/mysql-test/main/partition_cache_myisam.result new file mode 100644 index 00000000000..d8e4f837ff7 --- /dev/null +++ b/mysql-test/main/partition_cache_myisam.result @@ -0,0 +1,156 @@ +SET global query_cache_type=ON; +SET local query_cache_type=ON; +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 + 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 + 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; +SET global query_cache_type=default; -- cgit v1.2.1