WL# 2986

Final patch
-----------

This WL is about using this bitmap in all parts of the partition handler.
Thus for:
rnd_init/rnd_next
index_init/index_next and all other variants of index scans
read_range_... the various range scans implemented in the partition handler.

Also use those bitmaps in the various other calls that currently loop over all
partitions.




mysql-test/r/partition_pruning.result:
  WL# 2986
  
  New results reflecting bitmap being used to determine if a partition is to be
  included
mysql-test/t/partition_pruning.test:
  WL# 2986
  
  New tests to show bitmap being used in ha_partition
sql/ha_partition.cc:
  WL# 2986
  
  Used work from 2682 and removed the partition select code.
      
  Added do {} while loop to any iteration over partitions to now utilise 
  m_part_info->used_partitions bitmap to determine if a partition should be
  used.
sql/ha_partition.h:
  WL# 2986
  
  Removed unused member
sql/handler.h:
  WL# 2986
  
  Removed unused member
sql/opt_range.cc:
  WL# 2986
  
  Added bitmap_clear_all to clear bitmap prior to pruning
  DBUG code for testing
sql/sql_partition.cc:
  WL# 2986
  
  Changed < to <=, which fixes the problem with edge cases going awry.

2 files changed, 376 insertions, 38 deletions

diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index 6f210a76778..282e9775825 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -11,10 +11,10 @@ a int(11) not null
 insert into t1 values (1),(2),(3);
 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	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
 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	p0	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where a=1 or a=2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0,p1	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -42,10 +42,10 @@ partition p1 values less than (20)
 insert into t3 values (5),(15);
 explain partitions select * from t3 where a=11;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t3 where a=10;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t3 where a=20;
 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
@@ -59,11 +59,11 @@ partition p1 values in (14)
 insert into t4 values (10,2), (10,4);
 explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t4 
 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
 or (a=10 and b = 4);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -89,25 +89,25 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 explain partitions select * from t5
 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
 or (a=10 and b = 4);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp0,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
 explain partitions select * from t5 where (c=1 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p1_sp0	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p1_sp0	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
 (c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
 (b=2 and c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
 create table t6 (a int not null) partition by LIST(a) (
 partition p1 values in (1),
 partition p3 values in (3),
@@ -121,31 +121,31 @@ 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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	t6	p9	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	3	Using where
+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	t6	p7,p9	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	3	Using where
+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
@@ -159,16 +159,16 @@ 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	t7	p10	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
@@ -183,7 +183,7 @@ 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	t7	p30	ALL	NULL	NULL	NULL	NULL	3	Using where
+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),
@@ -192,7 +192,7 @@ partition p2 values less than (2000)
 insert into t8 values ('1985-05-05'),('1995-05-05');
 explain partitions select * from t8 where a < '1980-02-02';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t8	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
 partition p0 values less than (732299), -- 2004-12-19
 partition p1 values less than (732468), -- 2005-06-06
@@ -201,7 +201,7 @@ partition p2 values less than (732664)  -- 2005-12-19
 insert into t9 values ('2005-05-05'), ('2005-04-04');
 explain partitions select * from t9 where a <  '2004-12-19';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t9	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t9 where a <= '2004-12-19';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t9	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -224,10 +224,10 @@ partition p2 values less than (9)
 insert into t1 values (1),(2),(3);
 explain partitions select * from t1 where a1 > 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t1 where a1 >= 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1,p2	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where a1 < 3 and a1 > 3;
 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
@@ -242,19 +242,19 @@ partition p3 values in (4)
 insert into t3 values (1,1),(2,2),(3,3);
 explain partitions select * from t3 where a=2 or b=1;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p0_sp1,p1_sp0,p1_sp1,p1_sp2,p1_sp3,p2_sp1,p3_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p0_sp1,p1_sp0,p1_sp1,p1_sp2,p1_sp3,p2_sp1,p3_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t3 where a=4 or b=2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p0_sp2,p1_sp2,p2_sp2,p3_sp0,p3_sp1,p3_sp2,p3_sp3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p0_sp2,p1_sp2,p2_sp2,p3_sp0,p3_sp1,p3_sp2,p3_sp3	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1_sp2,p3_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p1_sp2,p3_sp1	system	NULL	NULL	NULL	NULL	1	
 drop table t3;
 create table t1 (a int) partition by hash(a) partitions 2;
 insert into t1 values (1),(2);
 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	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 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	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -266,25 +266,25 @@ explain partitions
 select * from t1 X, t1 Y 
 where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	X	p1,p2	ALL	a,b	NULL	NULL	NULL	4	Using where
+1	SIMPLE	X	p1,p2	ALL	a,b	NULL	NULL	NULL	2	Using where
 1	SIMPLE	Y	p2,p3	ref	a,b	b	4	test.X.b	2	Using where
 explain partitions
 select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	X	p1,p2	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	X	p1,p2	ALL	a	NULL	NULL	NULL	2	Using where
 1	SIMPLE	Y	p1,p2	ref	a	a	4	test.X.a	2	
 drop table t1;
 create table t1 (a int) partition by hash(a) partitions 20;
 insert into t1 values (1),(2),(3);
 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	p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
 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	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	2	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	p2,p3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	2	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	p1,p2,p3	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -300,7 +300,213 @@ partition p3 values in (3)
 insert into t1 values (1,1),(2,2),(3,3);
 explain partitions select * from t1 where b >  1 and b < 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p0_sp2,p1_sp2,p2_sp2,p3_sp2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p0_sp2,p1_sp2,p2_sp2,p3_sp2	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where b >  1 and b < 3 and (a =1 or a =2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1_sp2,p2_sp2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1_sp2,p2_sp2	system	NULL	NULL	NULL	NULL	1	
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+`a` int(11) default NULL
+);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+DROP TABLE IF EXISTS `t2`;
+Warnings:
+Note	1051	Unknown table 't2'
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+KEY `a` (`a`)
+) ;
+insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
+insert into t1 select a from t2;
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+`b` int(11) default NULL
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+insert into t2 select a,1 from t1 where a < 200;
+insert into t2 select a,2 from t1 where a >= 200 and a < 400;
+insert into t2 select a,3 from t1 where a >= 400 and a < 600;
+insert into t2 select a,4 from t1 where a >= 600 and a < 800;
+insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
+explain partitions select * from t2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	1010	
+explain partitions select * from t2 where a < 801 and a > 200;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	800	Using where
+explain partitions select * from t2 where a < 801 and a > 800;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p4	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where a > 600;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where b = 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	1010	Using where
+flush status;
+update t2 set b = 100 where b = 6;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set a = 1002 where a = 1001;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set b = 6 where a = 600;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set b = 6 where a > 600 and a < 800;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+delete from t2 where a > 600;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+`b` int(11) default NULL,
+index (b)
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+insert into t2 select a,1 from t1 where a < 100;
+insert into t2 select a,2 from t1 where a >= 200 and a < 300;
+insert into t2 select a,3 from t1 where a >= 300 and a < 400;
+insert into t2 select a,4 from t1 where a >= 400 and a < 500;
+insert into t2 select a,5 from t1 where a >= 500 and a < 600;
+insert into t2 select a,6 from t1 where a >= 600 and a < 700;
+insert into t2 select a,7 from t1 where a >= 700 and a < 800;
+insert into t2 select a,8 from t1 where a >= 800 and a < 900;
+insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
+explain partitions select * from t2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	910	
+explain partitions select * from t2 where a = 101;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0	ALL	NULL	NULL	NULL	NULL	110	Using where
+explain partitions select * from t2 where a = 550;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p2	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where a = 833;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p4	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where (a = 100 OR a = 900);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p4	ALL	NULL	NULL	NULL	NULL	310	Using where
+explain partitions select * from t2 where (a > 100 AND a < 600);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	710	Using where
+explain partitions select * from t2 where b = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ref	b	b	5	const	76	Using where
+explain partitions select * from t2 where b = 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ref	b	b	5	const	76	Using where
+explain partitions select * from t2 where b in (1,3,5);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b in (2,4,6);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b in (7,8,9);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5 and b < 8;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5 and b < 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	range	b	b	5	NULL	76	Using where
+explain partitions select * from t2 where b > 0 and b < 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+flush status;
+update t2 set a = 111 where b = 10;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+update t2 set a = 111 where b in (5,6);
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	915
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+flush status;
+update t2 set a = 222 where b = 7;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+delete from t2 where b = 7;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+delete from t2 where b > 5;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1215
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+show status like 'Handler_read_prev';
+Variable_name	Value
+Handler_read_prev	0
+show status like 'Handler_read_next';
+Variable_name	Value
+Handler_read_next	0
+flush status;
+delete from t2 where b < 5 or b > 3;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1215
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+show status like 'Handler_read_prev';
+Variable_name	Value
+Handler_read_prev	0
+show status like 'Handler_read_next';
+Variable_name	Value
+Handler_read_next	0
+drop table t1, t2;
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index b45e6cf0d76..55353a1a402 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -270,5 +270,137 @@ insert into t1 values (1,1),(2,2),(3,3);
 explain partitions select * from t1 where b >  1 and b < 3;
 explain partitions select * from t1 where b >  1 and b < 3 and (a =1 or a =2);
 
+# WL# 2986
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+  `a` int(11) default NULL
+);
+
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  KEY `a` (`a`)
+) ;
+
+insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
+
+insert into t1 select a from t2;
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 200;
+insert into t2 select a,2 from t1 where a >= 200 and a < 400;
+insert into t2 select a,3 from t1 where a >= 400 and a < 600;
+insert into t2 select a,4 from t1 where a >= 600 and a < 800;
+insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
+
+explain partitions select * from t2;
+explain partitions select * from t2 where a < 801 and a > 200;
+explain partitions select * from t2 where a < 801 and a > 800;
+explain partitions select * from t2 where a > 600;
+explain partitions select * from t2 where a > 600 and b = 1;
+explain partitions select * from t2 where a > 600 and b = 4;
+explain partitions select * from t2 where a > 600 and b = 5;
+explain partitions select * from t2 where b = 5;
+
+flush status;
+update t2 set b = 100 where b = 6;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set a = 1002 where a = 1001;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a = 600;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a > 600 and a < 800;
+show status like 'Handler_read_rnd_next';
+flush status;
+delete from t2 where a > 600;
+show status like 'Handler_read_rnd_next';
+
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL,
+  index (b)
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 100;
+insert into t2 select a,2 from t1 where a >= 200 and a < 300;
+insert into t2 select a,3 from t1 where a >= 300 and a < 400;
+insert into t2 select a,4 from t1 where a >= 400 and a < 500;
+insert into t2 select a,5 from t1 where a >= 500 and a < 600;
+insert into t2 select a,6 from t1 where a >= 600 and a < 700;
+insert into t2 select a,7 from t1 where a >= 700 and a < 800;
+insert into t2 select a,8 from t1 where a >= 800 and a < 900;
+insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
+
+explain partitions select * from t2;
+# not using indexes
+explain partitions select * from t2 where a = 101;
+explain partitions select * from t2 where a = 550;
+explain partitions select * from t2 where a = 833;
+explain partitions select * from t2 where (a = 100 OR a = 900);
+explain partitions select * from t2 where (a > 100 AND a < 600);
+explain partitions select * from t2 where b = 4;
+explain partitions select * from t2 where b = 6;
+explain partitions select * from t2 where b in (1,3,5);
+explain partitions select * from t2 where b in (2,4,6);
+explain partitions select * from t2 where b in (7,8,9);
+explain partitions select * from t2 where b > 5;
+explain partitions select * from t2 where b > 5 and b < 8;
+explain partitions select * from t2 where b > 5 and b < 7;
+explain partitions select * from t2 where b > 0 and b < 5;
+
+flush status;
+update t2 set a = 111 where b = 10;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 111 where b in (5,6);
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 222 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b > 5;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+flush status;
+delete from t2 where b < 5 or b > 3;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+
+drop table t1, t2;
 # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
 # being fixed.