BUG20733: Bug in partition pruning with zerofill field

Problem was with handling NULL values in ranges


mysql-test/r/partition_hash.result:
  New partition pruning test cases
mysql-test/r/partition_list.result:
  New partition pruning test cases
mysql-test/r/partition_pruning.result:
  New partition pruning test cases
mysql-test/r/partition_range.result:
  New partition pruning test cases
mysql-test/t/partition_hash.test:
  New partition pruning test cases
mysql-test/t/partition_list.test:
  New partition pruning test cases
mysql-test/t/partition_pruning.test:
  New partition pruning test cases
mysql-test/t/partition_range.test:
  New partition pruning test cases
sql/opt_range.cc:
  Added comment
sql/sql_partition.cc:
  Partition pruning didn't handle ranges with NULL values in a proper manner

1 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index 976466e1578..dd79e8d3acb 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -137,6 +137,32 @@ explain partitions select * from t6 where a >= 3 and a <= 8;
 
 explain partitions select * from t6 where a > 3 and a < 5;
 
+drop table t6;
+
+create table t6 (a int unsigned not null) partition by LIST(a) (
+  partition p1 values in (1),
+  partition p3 values in (3),
+  partition p5 values in (5),
+  partition p7 values in (7),
+  partition p9 values in (9)
+);
+insert into t6 values (1),(3),(5);
+
+explain partitions select * from t6 where a <  1;
+explain partitions select * from t6 where a <= 1;
+explain partitions select * from t6 where a >  9;
+explain partitions select * from t6 where a >= 9;
+
+explain partitions select * from t6 where a > 0 and a < 5;
+explain partitions select * from t6 where a > 5 and a < 12;
+explain partitions select * from t6 where a > 3 and a < 8 ;
+
+explain partitions select * from t6 where a >= 0 and a <= 5;
+explain partitions select * from t6 where a >= 5 and a <= 12;
+explain partitions select * from t6 where a >= 3 and a <= 8;
+
+explain partitions select * from t6 where a > 3 and a < 5;
+
 # RANGE(field) partitioning, interval analysis.
 create table t7 (a int not null) partition by RANGE(a) (
   partition p10 values less than (10),
@@ -162,6 +188,32 @@ explain partitions select * from t7 where a >= 90;
 # misc intervals
 explain partitions select * from t7 where a > 11 and a < 29;
 
+drop table t7;
+
+create table t7 (a int unsigned not null) 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 t7 values (10),(30),(50);
+
+# leftmost intervals
+explain partitions select * from t7 where a < 5;
+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 < 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;
+
+# misc intervals
+explain partitions select * from t7 where a > 11 and a < 29;
+
 # LIST(monontonic_func) partitioning
 create table t8 (a date not null) partition by RANGE(YEAR(a)) (
   partition p0 values less than (1980),