diff options
Diffstat (limited to 'mysql-test/r/partition_pruning.result')
| -rw-r--r-- | mysql-test/r/partition_pruning.result | 114 |
1 files changed, 114 insertions, 0 deletions
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 0a4cf9932c0..e52c2c7d886 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -3302,6 +3302,120 @@ 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; +# # MDEV-6239: Partition pruning is not working as expected in an inner query # create table t1 |