diff options
author | unknown <patg@govinda.patg.net> | 2006-01-28 16:22:32 -0800 |
---|---|---|
committer | unknown <patg@govinda.patg.net> | 2006-01-28 16:22:32 -0800 |
commit | ff5d012454023c4f89edd16659d470eb0db7a763 (patch) | |
tree | fe8077020fa0067a351ca1942c648c78d82ec3da /mysql-test/t/partition_pruning.test | |
parent | 9f59664b3666332f5bf91022cb365d07706f9029 (diff) | |
download | mariadb-git-ff5d012454023c4f89edd16659d470eb0db7a763.tar.gz |
WL# 2986
Final patch
-----------
This WL is about using this bitmap in all parts of the partition handler.
Thus for:
rnd_init/rnd_next
index_init/index_next and all other variants of index scans
read_range_... the various range scans implemented in the partition handler.
Also use those bitmaps in the various other calls that currently loop over all
partitions.
mysql-test/r/partition_pruning.result:
WL# 2986
New results reflecting bitmap being used to determine if a partition is to be
included
mysql-test/t/partition_pruning.test:
WL# 2986
New tests to show bitmap being used in ha_partition
sql/ha_partition.cc:
WL# 2986
Used work from 2682 and removed the partition select code.
Added do {} while loop to any iteration over partitions to now utilise
m_part_info->used_partitions bitmap to determine if a partition should be
used.
sql/ha_partition.h:
WL# 2986
Removed unused member
sql/handler.h:
WL# 2986
Removed unused member
sql/opt_range.cc:
WL# 2986
Added bitmap_clear_all to clear bitmap prior to pruning
DBUG code for testing
sql/sql_partition.cc:
WL# 2986
Changed < to <=, which fixes the problem with edge cases going awry.
Diffstat (limited to 'mysql-test/t/partition_pruning.test')
-rw-r--r-- | mysql-test/t/partition_pruning.test | 132 |
1 files changed, 132 insertions, 0 deletions
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index b45e6cf0d76..55353a1a402 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -270,5 +270,137 @@ insert into t1 values (1,1),(2,2),(3,3); explain partitions select * from t1 where b > 1 and b < 3; explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2); +# WL# 2986 +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` int(11) default NULL +); + +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +DROP TABLE IF EXISTS `t2`; +CREATE TABLE `t2` ( + `a` int(11) default NULL, + KEY `a` (`a`) +) ; + +insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ; + +insert into t1 select a from t2; + +DROP TABLE IF EXISTS `t2`; +CREATE TABLE `t2` ( + `a` int(11) default NULL, + `b` int(11) default NULL +) +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (200), +PARTITION p1 VALUES LESS THAN (400), +PARTITION p2 VALUES LESS THAN (600), +PARTITION p3 VALUES LESS THAN (800), +PARTITION p4 VALUES LESS THAN (1001)); + +insert into t2 select a,1 from t1 where a < 200; +insert into t2 select a,2 from t1 where a >= 200 and a < 400; +insert into t2 select a,3 from t1 where a >= 400 and a < 600; +insert into t2 select a,4 from t1 where a >= 600 and a < 800; +insert into t2 select a,5 from t1 where a >= 800 and a < 1001; + +explain partitions select * from t2; +explain partitions select * from t2 where a < 801 and a > 200; +explain partitions select * from t2 where a < 801 and a > 800; +explain partitions select * from t2 where a > 600; +explain partitions select * from t2 where a > 600 and b = 1; +explain partitions select * from t2 where a > 600 and b = 4; +explain partitions select * from t2 where a > 600 and b = 5; +explain partitions select * from t2 where b = 5; + +flush status; +update t2 set b = 100 where b = 6; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set a = 1002 where a = 1001; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set b = 6 where a = 600; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set b = 6 where a > 600 and a < 800; +show status like 'Handler_read_rnd_next'; +flush status; +delete from t2 where a > 600; +show status like 'Handler_read_rnd_next'; + + +DROP TABLE IF EXISTS `t2`; +CREATE TABLE `t2` ( + `a` int(11) default NULL, + `b` int(11) default NULL, + index (b) +) +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (200), +PARTITION p1 VALUES LESS THAN (400), +PARTITION p2 VALUES LESS THAN (600), +PARTITION p3 VALUES LESS THAN (800), +PARTITION p4 VALUES LESS THAN (1001)); + +insert into t2 select a,1 from t1 where a < 100; +insert into t2 select a,2 from t1 where a >= 200 and a < 300; +insert into t2 select a,3 from t1 where a >= 300 and a < 400; +insert into t2 select a,4 from t1 where a >= 400 and a < 500; +insert into t2 select a,5 from t1 where a >= 500 and a < 600; +insert into t2 select a,6 from t1 where a >= 600 and a < 700; +insert into t2 select a,7 from t1 where a >= 700 and a < 800; +insert into t2 select a,8 from t1 where a >= 800 and a < 900; +insert into t2 select a,9 from t1 where a >= 900 and a < 1001; + +explain partitions select * from t2; +# not using indexes +explain partitions select * from t2 where a = 101; +explain partitions select * from t2 where a = 550; +explain partitions select * from t2 where a = 833; +explain partitions select * from t2 where (a = 100 OR a = 900); +explain partitions select * from t2 where (a > 100 AND a < 600); +explain partitions select * from t2 where b = 4; +explain partitions select * from t2 where b = 6; +explain partitions select * from t2 where b in (1,3,5); +explain partitions select * from t2 where b in (2,4,6); +explain partitions select * from t2 where b in (7,8,9); +explain partitions select * from t2 where b > 5; +explain partitions select * from t2 where b > 5 and b < 8; +explain partitions select * from t2 where b > 5 and b < 7; +explain partitions select * from t2 where b > 0 and b < 5; + +flush status; +update t2 set a = 111 where b = 10; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +update t2 set a = 111 where b in (5,6); +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +update t2 set a = 222 where b = 7; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +delete from t2 where b = 7; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +delete from t2 where b > 5; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +show status like 'Handler_read_prev'; +show status like 'Handler_read_next'; +flush status; +delete from t2 where b < 5 or b > 3; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +show status like 'Handler_read_prev'; +show status like 'Handler_read_next'; + +drop table t1, t2; # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447 # being fixed. |