BUG#18558 "Partition pruning results are incorrect for certain class of WHERE clauses" :

* Produce right results for conditions that were transformed to "(partitioning_range) AND
  (list_of_subpartitioning_ranges)": make each partition id set iterator auto-reset itself
  after it has returned all partition ids in the sequence 
* Fix "Range mapping" and "Range mapping" partitioning interval analysis functions to 
  correctly deal with NULL values. 


mysql-test/r/partition_pruning.result:
  Testcase for BUG#18558
mysql-test/t/partition_pruning.test:
  Testcase for BUG#18558
sql/opt_range.cc:
  BUG#18558: Move partition set iterator initialization to sql_partition.cc, comment fixes
sql/partition_info.h:
  BUG#18558: Make each partition set iterator auto-reset itself after it has returned all 
  partition ids in the set it enumerates.
sql/sql_partition.cc:
  BUG#18558: 
  - Make each partition set iterator auto-reset itself after it has returned all 
    partition ids in the set it enumerates. 
  - Fix partition interval analysis to correctly handle intervals with one or both
    NULL bounds.
sql/sql_partition.h:
  BUG#18558: 
   - Make each partition set iterator auto-reset itself after it has returned all 
     partition ids in the set it enumerates. 
   - Rename PARTITION_ITERATOR::has_null_value to ret_null_part

1 files changed, 62 insertions, 0 deletions

diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index d520f7735ec..8b959cb6a3e 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -597,3 +597,65 @@ NULL
 explain partitions select * from t1 where f_int1 is null;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	part4_p2sp0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (a int not null, b int not null)
+partition by list(a) 
+subpartition by hash(b) subpartitions 4 
+(
+partition p0 values in (1),
+partition p1 values in (2),
+partition p2 values in (3)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+(2,1),(2,2),(2,3),(2,4);
+explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp1,p0_p0sp2	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
+create table t1 (a int, b int not null)
+partition by list(a) 
+subpartition by hash(b) subpartitions 2
+(
+partition p0 values in (1),
+partition p1 values in (2),
+partition p2 values in (3),
+partition pn values in (NULL)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+(2,1),(2,2),(2,3),(2,4), (NULL,1);
+explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pn_p3sp0,pn_p3sp1	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pn_p3sp0,pn_p3sp1	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp0,p0_p0sp1,pn_p3sp0,pn_p3sp1	ALL	NULL	NULL	NULL	NULL	5	Using where
+explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp0,p0_p0sp1,pn_p3sp0,pn_p3sp1	ALL	NULL	NULL	NULL	NULL	5	Using where
+drop table t1;
+create table t1 ( a int)  partition by list (MOD(a, 10))
+( partition p0  values in (0), partition p1 values in (1),
+partition p2 values in (2), partition p3 values in (3),
+partition p4 values in (4), partition p5 values in (5),
+partition p6 values in (6), partition pn values in (NULL)
+);
+insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
+explain partitions select * from t1 where a is null or a < 2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,pn	ALL	NULL	NULL	NULL	NULL	8	Using where
+drop table t1;
+create table t1 (s1 int) partition by list (s1) 
+(partition p1 values in (0), 
+partition p2 values in (1), 
+partition p3 values in (null));
+insert into t1 values (0),(1),(null);
+select count(*) from t1 where s1 < 0 or s1 is null;
+count(*)
+1
+explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	system	NULL	NULL	NULL	NULL	1	
+drop table t1;