summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/partition_innodb.result49
-rw-r--r--mysql-test/r/partition_pruning.result84
-rw-r--r--mysql-test/t/partition_innodb.test32
-rw-r--r--mysql-test/t/partition_pruning.test28
-rw-r--r--sql/sql_partition.cc11
5 files changed, 202 insertions, 2 deletions
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 6167b4cef95..2a04aafe554 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -1,5 +1,54 @@
drop table if exists t1, t2;
#
+# Bug#51830: Incorrect partition pruning on range partition (regression)
+#
+CREATE TABLE t1 (a INT NOT NULL)
+ENGINE = InnoDB
+PARTITION BY RANGE(a)
+(PARTITION p10 VALUES LESS THAN (10),
+PARTITION p30 VALUES LESS THAN (30),
+PARTITION p50 VALUES LESS THAN (50),
+PARTITION p70 VALUES LESS THAN (70),
+PARTITION p90 VALUES LESS THAN (90));
+INSERT INTO t1 VALUES (10),(30),(50);
+INSERT INTO t1 VALUES (70);
+INSERT INTO t1 VALUES (80);
+INSERT INTO t1 VALUES (89);
+INSERT INTO t1 VALUES (90);
+ERROR HY000: Table has no partition for value 90
+INSERT INTO t1 VALUES (100);
+ERROR HY000: Table has no partition for value 100
+insert INTO t1 VALUES (110);
+ERROR HY000: Table has no partition for value 110
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
+DROP TABLE t1;
+#
# Bug#50104: Partitioned table with just 1 partion works with fk
#
CREATE TABLE t2 (
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index b46c1ba8005..568c21b27be 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -2101,6 +2101,21 @@ insert into t7 values (10),(30),(50);
explain partitions select * from t7 where a < 5;
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 t7 where a < 9;
+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 t7 where a <= 9;
+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 t7 where a = 9;
+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 t7 where a >= 9;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a > 9;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t7 where a < 10;
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
@@ -2110,9 +2125,33 @@ id select_type table partitions type possible_keys key key_len ref rows Extra
explain partitions select * from t7 where a = 10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t7 p30 system NULL NULL NULL NULL 1
+explain partitions select * from t7 where a >= 10;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a > 10;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a < 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a <= 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a = 89;
+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 t7 where a > 89;
+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 t7 where a >= 89;
+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 t7 where a < 90;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a <= 90;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t7 where a = 90;
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
@@ -2122,6 +2161,9 @@ id select_type table partitions type possible_keys key key_len ref rows Extra
explain partitions select * from t7 where a >= 90;
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 t7 where a > 91;
+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 t7 where a > 11 and a < 29;
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
@@ -2137,6 +2179,21 @@ insert into t7 values (10),(30),(50);
explain partitions select * from t7 where a < 5;
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 t7 where a < 9;
+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 t7 where a <= 9;
+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 t7 where a = 9;
+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 t7 where a >= 9;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a > 9;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t7 where a < 10;
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
@@ -2146,9 +2203,33 @@ id select_type table partitions type possible_keys key key_len ref rows Extra
explain partitions select * from t7 where a = 10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t7 p30 system NULL NULL NULL NULL 1
+explain partitions select * from t7 where a >= 10;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a > 10;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a < 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a <= 89;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a = 89;
+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 t7 where a > 89;
+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 t7 where a >= 89;
+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 t7 where a < 90;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t7 where a <= 90;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t7 where a = 90;
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
@@ -2158,6 +2239,9 @@ id select_type table partitions type possible_keys key key_len ref rows Extra
explain partitions select * from t7 where a >= 90;
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 t7 where a > 91;
+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 t7 where a > 11 and a < 29;
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
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index 8e92adc5868..e1ac7b4c7eb 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -8,6 +8,38 @@ drop table if exists t1, t2;
let $MYSQLD_DATADIR= `SELECT @@datadir`;
--echo #
+--echo # Bug#51830: Incorrect partition pruning on range partition (regression)
+--echo #
+CREATE TABLE t1 (a INT NOT NULL)
+ENGINE = InnoDB
+PARTITION BY RANGE(a)
+(PARTITION p10 VALUES LESS THAN (10),
+ PARTITION p30 VALUES LESS THAN (30),
+ PARTITION p50 VALUES LESS THAN (50),
+ PARTITION p70 VALUES LESS THAN (70),
+ PARTITION p90 VALUES LESS THAN (90));
+INSERT INTO t1 VALUES (10),(30),(50);
+INSERT INTO t1 VALUES (70);
+INSERT INTO t1 VALUES (80);
+INSERT INTO t1 VALUES (89);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO t1 VALUES (90);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO t1 VALUES (100);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+insert INTO t1 VALUES (110);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
+DROP TABLE t1;
+
+--echo #
--echo # Bug#50104: Partitioned table with just 1 partion works with fk
--echo #
CREATE TABLE t2 (
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index 315b8393d93..358832496e5 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -702,15 +702,29 @@ insert into t7 values (10),(30),(50);
# leftmost intervals
explain partitions select * from t7 where a < 5;
+explain partitions select * from t7 where a < 9;
+explain partitions select * from t7 where a <= 9;
+explain partitions select * from t7 where a = 9;
+explain partitions select * from t7 where a >= 9;
+explain partitions select * from t7 where a > 9;
explain partitions select * from t7 where a < 10;
explain partitions select * from t7 where a <= 10;
explain partitions select * from t7 where a = 10;
+explain partitions select * from t7 where a >= 10;
+explain partitions select * from t7 where a > 10;
#rightmost intervals
+explain partitions select * from t7 where a < 89;
+explain partitions select * from t7 where a <= 89;
+explain partitions select * from t7 where a = 89;
+explain partitions select * from t7 where a > 89;
+explain partitions select * from t7 where a >= 89;
explain partitions select * from t7 where a < 90;
+explain partitions select * from t7 where a <= 90;
explain partitions select * from t7 where a = 90;
explain partitions select * from t7 where a > 90;
explain partitions select * from t7 where a >= 90;
+explain partitions select * from t7 where a > 91;
# misc intervals
explain partitions select * from t7 where a > 11 and a < 29;
@@ -728,15 +742,29 @@ insert into t7 values (10),(30),(50);
# leftmost intervals
explain partitions select * from t7 where a < 5;
+explain partitions select * from t7 where a < 9;
+explain partitions select * from t7 where a <= 9;
+explain partitions select * from t7 where a = 9;
+explain partitions select * from t7 where a >= 9;
+explain partitions select * from t7 where a > 9;
explain partitions select * from t7 where a < 10;
explain partitions select * from t7 where a <= 10;
explain partitions select * from t7 where a = 10;
+explain partitions select * from t7 where a >= 10;
+explain partitions select * from t7 where a > 10;
#rightmost intervals
+explain partitions select * from t7 where a < 89;
+explain partitions select * from t7 where a <= 89;
+explain partitions select * from t7 where a = 89;
+explain partitions select * from t7 where a > 89;
+explain partitions select * from t7 where a >= 89;
explain partitions select * from t7 where a < 90;
+explain partitions select * from t7 where a <= 90;
explain partitions select * from t7 where a = 90;
explain partitions select * from t7 where a > 90;
explain partitions select * from t7 where a >= 90;
+explain partitions select * from t7 where a > 91;
# misc intervals
explain partitions select * from t7 where a > 11 and a < 29;
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index f12194d7771..395156dcd63 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -2876,6 +2876,7 @@ int get_partition_id_range(partition_info *part_info,
*func_value= part_func_value;
if (unsigned_flag)
part_func_value-= 0x8000000000000000ULL;
+ /* Search for the partition containing part_func_value */
while (max_part_id > min_part_id)
{
loc_part_id= (max_part_id + min_part_id) / 2;
@@ -3015,11 +3016,17 @@ uint32 get_partition_id_range_for_endpoint(partition_info *part_info,
part_end_val= range_array[loc_part_id];
if (left_endpoint)
{
+ DBUG_ASSERT(part_func_value > part_end_val ?
+ (loc_part_id == max_partition &&
+ !part_info->defined_max_value) :
+ 1);
/*
In case of PARTITION p VALUES LESS THAN MAXVALUE
- the maximum value is in the current partition.
+ the maximum value is in the current (last) partition.
+ If value is equal or greater than the endpoint,
+ the range starts from the next partition.
*/
- if (part_func_value == part_end_val &&
+ if (part_func_value >= part_end_val &&
(loc_part_id < max_partition || !part_info->defined_max_value))
loc_part_id++;
}
View Lorry Controller Status