Bug#51830: Incorrect partition pruning on range partition

(regression)

Problem was that partition pruning did not exclude the
last partition if the range was beyond it
(i.e. not using MAXVALUE)

Fix was to not include the last partition if the
partitioning function value was not within the partition
range.

mysql-test/r/partition_innodb.result:
  Bug#51830: Incorrect partition pruning on range partition
  (regression)
  
  Updated result
mysql-test/r/partition_pruning.result:
  Bug#51830: Incorrect partition pruning on range partition
  (regression)
  
  Updated result
mysql-test/t/partition_innodb.test:
  Bug#51830: Incorrect partition pruning on range partition
  (regression)
  
  Added test for pruning in InnoDB, since it does not show
  for MyISAM due to 'Impossible WHERE noticed after reading
  const tables'.
mysql-test/t/partition_pruning.test:
  Bug#51830: Incorrect partition pruning on range partition
  (regression)
  
  Added test
sql/sql_partition.cc:
  Bug#51830: Incorrect partition pruning on range partition
  (regression)
  
  Also increase the partition id if not inside the last partition
  (and no MAXVALUE is defined).
  
  Added comments and DBUG_ASSERT.

2 files changed, 60 insertions, 0 deletions

diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index b7fe4477a13..0eec30b69bf 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -7,6 +7,38 @@ drop table if exists t1;
 
 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;
+
 #
 # Bug#47029: Crash when reorganize partition with subpartition
 #
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;