From 548db492101346bf582295544c9a7a42d3308641 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 6 May 2014 11:05:37 +0200 Subject: Bug#17909699: WRONG RESULTS WITH PARTITION BY LIST COLUMNS() Typo leading to not including the last list values (partition). Also improved pruning to skip last partition if not used. rb#4762 approved by Aditya and Marko. --- mysql-test/r/partition_pruning.result | 114 ++++++++++++++++++++++++++++++++++ 1 file changed, 114 insertions(+) (limited to 'mysql-test/r/partition_pruning.result') diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index fde7a3b8104..3d572726507 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -3294,3 +3294,117 @@ explain partitions select * from t1 where a between 10 and 10+33; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where drop table t0, t1; +# +# Bug#71095: Wrong results with PARTITION BY LIST COLUMNS() +# +CREATE TABLE t1 +(c1 int, +c2 int, +c3 int, +c4 int, +PRIMARY KEY (c1,c2)) +PARTITION BY LIST COLUMNS (c2) +(PARTITION p1 VALUES IN (1,2), +PARTITION p2 VALUES IN (3,4)); +INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1); +INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1); +SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1; +c1 c2 c3 c4 +SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; +c1 c2 c3 c4 +1 1 1 1 +SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; +c1 c2 c3 c4 +1 1 1 1 +SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; +c1 c2 c3 c4 +1 1 1 1 +1 2 1 1 +SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; +c1 c2 c3 c4 +1 2 1 1 +SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; +c1 c2 c3 c4 +1 1 1 1 +1 2 1 1 +SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; +c1 c2 c3 c4 +1 1 1 1 +1 2 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; +c1 c2 c3 c4 +2 3 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; +c1 c2 c3 c4 +2 3 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; +c1 c2 c3 c4 +2 3 1 1 +2 4 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; +c1 c2 c3 c4 +2 4 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; +c1 c2 c3 c4 +2 3 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; +c1 c2 c3 c4 +2 3 1 1 +2 4 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; +c1 c2 c3 c4 +2 4 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; +c1 c2 c3 c4 +2 4 1 1 +SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; +c1 c2 c3 c4 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 const PRIMARY PRIMARY 8 const,const 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; -- cgit v1.2.1