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

4 files changed, 334 insertions, 0 deletions

diff --git a/mysql-test/r/partition_hash.result b/mysql-test/r/partition_hash.result
index 08faccd024e..9a82a36d902 100644
--- a/mysql-test/r/partition_hash.result
+++ b/mysql-test/r/partition_hash.result
@@ -1,4 +1,88 @@
 drop table if exists t1;
+create table t1 (a int unsigned)
+partition by hash(a div 2)
+partitions 4;
+insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
+select * from t1 where a < 0;
+a
+select * from t1 where a is null or (a >= 5 and a <= 7);
+a
+NULL
+5
+6
+7
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a is not null;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+select * from t1 where a >= 1 and a < 3;
+a
+1
+2
+select * from t1 where a >= 3 and a <= 5;
+a
+3
+4
+5
+select * from t1 where a > 2 and a < 4;
+a
+3
+select * from t1 where a > 3 and a <= 6;
+a
+4
+5
+6
+select * from t1 where a > 5;
+a
+6
+7
+select * from t1 where a >= 1 and a <= 5;
+a
+1
+2
+3
+4
+5
+explain partitions select * from t1 where a < 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
+explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p2,p3	ALL	NULL	NULL	NULL	NULL	7	Using where
+explain partitions select * from t1 where a is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	3	Using where
+explain partitions select * from t1 where a is not null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
+explain partitions select * from t1 where a >= 1 and a < 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1	ALL	NULL	NULL	NULL	NULL	5	Using where
+explain partitions select * from t1 where a >= 3 and a <= 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain partitions select * from t1 where a > 2 and a < 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a > 3 and a <= 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain partitions select * from t1 where a > 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
+explain partitions select * from t1 where a >= 1 and a <= 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
+drop table t1;
 CREATE TABLE t1 (
 a int not null,
 b int not null,
diff --git a/mysql-test/r/partition_list.result b/mysql-test/r/partition_list.result
index c722a3c6be3..e64a7a8d154 100644
--- a/mysql-test/r/partition_list.result
+++ b/mysql-test/r/partition_list.result
@@ -1,4 +1,98 @@
 drop table if exists t1;
+create table t1 (a int unsigned)
+partition by list (a)
+(partition p0 values in (0),
+partition p1 values in (1),
+partition pnull values in (null),
+partition p2 values in (2));
+insert into t1 values (null),(0),(1),(2);
+select * from t1 where a < 2;
+a
+0
+1
+select * from t1 where a <= 0;
+a
+0
+select * from t1 where a < 1;
+a
+0
+select * from t1 where a > 0;
+a
+1
+2
+select * from t1 where a > 1;
+a
+2
+select * from t1 where a >= 0;
+a
+0
+1
+2
+select * from t1 where a >= 1;
+a
+1
+2
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a is not null;
+a
+0
+1
+2
+select * from t1 where a is null or a > 0;
+a
+1
+NULL
+2
+drop table t1;
+create table t1 (a int unsigned, b int)
+partition by list (a)
+subpartition by hash (b)
+subpartitions 2
+(partition p0 values in (0),
+partition p1 values in (1),
+partition pnull values in (null, 2),
+partition p3 values in (3));
+insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
+insert into t1 values (2,0),(2,1),(3,0),(3,1);
+explain partitions select * from t1 where a is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+select * from t1 where a is null;
+a	b
+NULL	0
+NULL	1
+explain partitions select * from t1 where a = 2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+select * from t1 where a = 2;
+a	b
+2	0
+2	1
+select * from t1 where a <= 0;
+a	b
+0	0
+0	1
+select * from t1 where a < 3;
+a	b
+0	0
+0	1
+1	0
+1	1
+2	0
+2	1
+select * from t1 where a >= 1 or a is null;
+a	b
+1	0
+1	1
+NULL	0
+2	0
+NULL	1
+2	1
+3	0
+3	1
+drop table t1;
 CREATE TABLE t1 (
 a int not null,
 b int not null,
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index ee294242bf7..bf7888bfd99 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -149,6 +149,48 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 explain partitions select * from t6 where a > 3 and 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
+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;
+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 t6 where a <= 1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p1	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t6 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 t6 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 t6 where a > 0 and a < 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p1,p3	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t6 where a > 5 and a < 12;
+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 t6 where a > 3 and a < 8 ;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p5,p7	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t6 where a >= 0 and a <= 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p1,p3,p5	ALL	NULL	NULL	NULL	NULL	3	Using where
+explain partitions select * from t6 where a >= 5 and a <= 12;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p5,p7,p9	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t6 where a >= 3 and a <= 8;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	p3,p5,p7	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t6 where a > 3 and 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
 create table t7 (a int not null) partition by RANGE(a) (
 partition p10 values less than (10),
 partition p30 values less than (30),
@@ -184,6 +226,42 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 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
+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);
+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 < 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
+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	p10,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	system	NULL	NULL	NULL	NULL	1	
+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
+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 >= 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 > 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
 create table t8 (a date not null) partition by RANGE(YEAR(a)) (
 partition p0 values less than (1980),
 partition p1 values less than (1990),
diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result
index 9123c894f32..9812c80040b 100644
--- a/mysql-test/r/partition_range.result
+++ b/mysql-test/r/partition_range.result
@@ -1,4 +1,82 @@
 drop table if exists t1;
+create table t1 (a int unsigned)
+partition by range (a)
+(partition pnull values less than (0),
+partition p0 values less than (1),
+partition p1 values less than(2));
+insert into t1 values (null),(0),(1);
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a >= 0;
+a
+0
+1
+select * from t1 where a < 0;
+a
+select * from t1 where a <= 0;
+a
+0
+select * from t1 where a > 1;
+a
+explain partitions select * from t1 where a is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a >= 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a < 0;
+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 a <= 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull,p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a > 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
+drop table t1;
+create table t1 (a int unsigned, b int unsigned)
+partition by range (a)
+subpartition by hash (b)
+subpartitions 2
+(partition pnull values less than (0),
+partition p0 values less than (1),
+partition p1 values less than(2));
+insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1);
+select * from t1 where a is null;
+a	b
+NULL	0
+NULL	1
+select * from t1 where a >= 0;
+a	b
+0	0
+0	1
+1	0
+1	1
+select * from t1 where a < 0;
+a	b
+select * from t1 where a <= 0;
+a	b
+0	0
+0	1
+select * from t1 where a > 1;
+a	b
+explain partitions select * from t1 where a is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a >= 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain partitions select * from t1 where a < 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a <= 0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain partitions select * from t1 where a > 1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1_p1sp0,p1_p1sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
 CREATE TABLE t1 (
 a int not null,
 b int not null,