Manual merge from mysql-trunk-merge.

Conflicts:
  - sql/sql_partition.cc

4 files changed, 771 insertions, 78 deletions

diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index d7790cd8075..bd81e2b5d4f 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -1,4 +1,614 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+#
+# Bug#49742: Partition Pruning not working correctly for RANGE
+#
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION p5 VALUES LESS THAN (6),
+PARTITION max VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
+SELECT * FROM t1 WHERE a < 1;
+a
+-1
+0
+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	p0	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a < 2;
+a
+-1
+0
+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	p0,p1	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a < 3;
+a
+-1
+0
+1
+2
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2	index	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
+SELECT * FROM t1 WHERE a < 4;
+a
+-1
+0
+1
+2
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	index	PRIMARY	PRIMARY	4	NULL	5	Using where; Using index
+SELECT * FROM t1 WHERE a < 5;
+a
+-1
+0
+1
+2
+3
+4
+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,p4	index	PRIMARY	PRIMARY	4	NULL	6	Using where; Using index
+SELECT * FROM t1 WHERE a < 6;
+a
+-1
+0
+1
+2
+3
+4
+5
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5	index	PRIMARY	PRIMARY	4	NULL	7	Using where; Using index
+SELECT * FROM t1 WHERE a < 7;
+a
+-1
+0
+1
+2
+3
+4
+5
+6
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,max	range	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 1;
+a
+-1
+0
+1
+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	p0,p1	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a <= 2;
+a
+-1
+0
+1
+2
+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,p1,p2	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a <= 3;
+a
+-1
+0
+1
+2
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a <= 4;
+a
+-1
+0
+1
+2
+3
+4
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a <= 5;
+a
+-1
+0
+1
+2
+3
+4
+5
+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,p4,p5	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a <= 6;
+a
+-1
+0
+1
+2
+3
+4
+5
+6
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,max	range	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 7;
+a
+-1
+0
+1
+2
+3
+4
+5
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,max	range	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a = 1;
+a
+1
+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	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 2;
+a
+2
+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	p2	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 3;
+a
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 4;
+a
+4
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 5;
+a
+5
+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	p5	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 6;
+a
+6
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 WHERE a = 7;
+a
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 WHERE a >= 1;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+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,p2,p3,p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 2;
+a
+2
+3
+4
+5
+6
+7
+8
+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	p2,p3,p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 3;
+a
+3
+4
+5
+6
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3,p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 4;
+a
+4
+5
+6
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 5;
+a
+5
+6
+7
+8
+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	p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 6;
+a
+6
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a >= 7;
+a
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a > 1;
+a
+2
+3
+4
+5
+6
+7
+8
+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	p2,p3,p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a > 2;
+a
+3
+4
+5
+6
+7
+8
+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	p3,p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a > 3;
+a
+4
+5
+6
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4,p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a > 4;
+a
+5
+6
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p5,max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a > 5;
+a
+6
+7
+8
+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	max	index	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index
+SELECT * FROM t1 WHERE a > 6;
+a
+7
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a > 7;
+a
+8
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION max VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
+SELECT * FROM t1 WHERE a < 1;
+a
+-1
+0
+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	p0	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a < 2;
+a
+-1
+0
+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	p0,p1	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a < 3;
+a
+-1
+0
+1
+2
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2	index	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
+SELECT * FROM t1 WHERE a < 4;
+a
+-1
+0
+1
+2
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	index	PRIMARY	PRIMARY	4	NULL	5	Using where; Using index
+SELECT * FROM t1 WHERE a < 5;
+a
+-1
+0
+1
+2
+3
+4
+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,p4	index	PRIMARY	PRIMARY	4	NULL	6	Using where; Using index
+SELECT * FROM t1 WHERE a < 6;
+a
+-1
+0
+1
+2
+3
+4
+5
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,max	range	PRIMARY	PRIMARY	4	NULL	8	Using where; Using index
+SELECT * FROM t1 WHERE a <= 1;
+a
+-1
+0
+1
+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	p0,p1	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 2;
+a
+-1
+0
+1
+2
+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,p1,p2	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 3;
+a
+-1
+0
+1
+2
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 4;
+a
+-1
+0
+1
+2
+3
+4
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a <= 5;
+a
+-1
+0
+1
+2
+3
+4
+5
+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,p4,max	range	PRIMARY	PRIMARY	4	NULL	8	Using where; Using index
+SELECT * FROM t1 WHERE a <= 6;
+a
+-1
+0
+1
+2
+3
+4
+5
+6
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,max	range	PRIMARY	PRIMARY	4	NULL	8	Using where; Using index
+SELECT * FROM t1 WHERE a = 1;
+a
+1
+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	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 2;
+a
+2
+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	p2	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 3;
+a
+3
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 4;
+a
+4
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4	system	PRIMARY	NULL	NULL	NULL	1	
+SELECT * FROM t1 WHERE a = 5;
+a
+5
+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	max	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 WHERE a = 6;
+a
+6
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 WHERE a >= 1;
+a
+1
+2
+3
+4
+5
+6
+7
+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,p2,p3,p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a >= 2;
+a
+2
+3
+4
+5
+6
+7
+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	p2,p3,p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a >= 3;
+a
+3
+4
+5
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3,p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a >= 4;
+a
+4
+5
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a >= 5;
+a
+5
+6
+7
+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	max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a >= 6;
+a
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a > 1;
+a
+2
+3
+4
+5
+6
+7
+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	p2,p3,p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a > 2;
+a
+3
+4
+5
+6
+7
+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	p3,p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a > 3;
+a
+4
+5
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4,max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a > 4;
+a
+5
+6
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	index	PRIMARY	PRIMARY	4	NULL	9	Using where; Using index
+SELECT * FROM t1 WHERE a > 5;
+a
+6
+7
+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	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a > 6;
+a
+7
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	max	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+DROP TABLE t1;
 # test of RANGE and index
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY RANGE (TO_DAYS(a))
@@ -2145,7 +2755,7 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	p0,p4	ALL	NULL	NULL	NULL	NULL	910	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	910	Using where
+1	SIMPLE	t2	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	910	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
diff --git a/mysql-test/suite/rpl/r/rpl_myisam_null_values.result b/mysql-test/suite/rpl/r/rpl_myisam_null_values.result
deleted file mode 100644
index 574528a8d79..00000000000
--- a/mysql-test/suite/rpl/r/rpl_myisam_null_values.result
+++ /dev/null
@@ -1,24 +0,0 @@
-stop slave;
-drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
-reset master;
-reset slave;
-drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
-start slave;
-CREATE TABLE t1 (c1 BIT, c2 INT);
-INSERT INTO `t1` VALUES ( 1, 1 );
-UPDATE t1 SET c1=NULL where c2=1;
-Comparing tables master:test.t1 and slave:test.t1
-DELETE FROM t1 WHERE c2=1 LIMIT 1;
-Comparing tables master:test.t1 and slave:test.t1
-DROP TABLE t1;
-CREATE TABLE t1 (c1 CHAR);
-INSERT INTO t1 ( c1 ) VALUES ( 'w' ) ;
-SELECT * FROM t1;
-c1
-w
-# should trigger switch to row due to LIMIT
-UPDATE t1 SET c1=NULL WHERE c1='w' LIMIT 2;
-Comparing tables master:test.t1 and slave:test.t1
-DELETE FROM t1 LIMIT 2;
-Comparing tables master:test.t1 and slave:test.t1
-DROP TABLE t1;
diff --git a/mysql-test/suite/rpl/t/rpl_myisam_null_values.test b/mysql-test/suite/rpl/t/rpl_myisam_null_values.test
deleted file mode 100644
index d9ec95fc510..00000000000
--- a/mysql-test/suite/rpl/t/rpl_myisam_null_values.test
+++ /dev/null
@@ -1,53 +0,0 @@
-# BUG#49481: RBR: MyISAM and bit fields may cause slave to stop on delete cant find record
-# BUG#49482: RBR: Replication may break on deletes when MyISAM tables + char field are used
-
--- source include/master-slave.inc
--- source include/have_binlog_format_mixed_or_row.inc
-
--- connection master
-CREATE TABLE t1 (c1 BIT, c2 INT);
-INSERT INTO `t1` VALUES ( 1, 1 );
-UPDATE t1 SET c1=NULL where c2=1;
--- sync_slave_with_master
-
--- let $diff_table_1=master:test.t1
--- let $diff_table_2=slave:test.t1
--- source include/diff_tables.inc
-
--- connection master
-DELETE FROM t1 WHERE c2=1 LIMIT 1;
--- sync_slave_with_master
-
--- let $diff_table_1=master:test.t1
--- let $diff_table_2=slave:test.t1
--- source include/diff_tables.inc
-
--- connection master
-DROP TABLE t1;
--- sync_slave_with_master
-
--- connection master
-
-CREATE TABLE t1 (c1 CHAR);
-
-INSERT INTO t1 ( c1 ) VALUES ( 'w' ) ;
-SELECT * FROM t1;
--- echo # should trigger switch to row due to LIMIT
-UPDATE t1 SET c1=NULL WHERE c1='w' LIMIT 2;
--- sync_slave_with_master
-
--- let $diff_table_1=master:test.t1
--- let $diff_table_2=slave:test.t1
--- source include/diff_tables.inc
-
--- connection master
-DELETE FROM t1 LIMIT 2;
--- sync_slave_with_master
-
--- let $diff_table_1=master:test.t1
--- let $diff_table_2=slave:test.t1
--- source include/diff_tables.inc
-
--- connection master
-DROP TABLE t1;
--- sync_slave_with_master
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index fd3f7f2c322..e1ba6025474 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -8,6 +8,166 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 --enable_warnings
 
+--echo #
+--echo # Bug#49742: Partition Pruning not working correctly for RANGE
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION p5 VALUES LESS THAN (6),
+PARTITION max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
+
+SELECT * FROM t1 WHERE a < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
+SELECT * FROM t1 WHERE a < 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+SELECT * FROM t1 WHERE a < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+SELECT * FROM t1 WHERE a < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+SELECT * FROM t1 WHERE a < 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+SELECT * FROM t1 WHERE a < 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+SELECT * FROM t1 WHERE a < 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+SELECT * FROM t1 WHERE a <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+SELECT * FROM t1 WHERE a <= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
+SELECT * FROM t1 WHERE a <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+SELECT * FROM t1 WHERE a <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+SELECT * FROM t1 WHERE a <= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
+SELECT * FROM t1 WHERE a <= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+SELECT * FROM t1 WHERE a <= 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
+SELECT * FROM t1 WHERE a = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
+SELECT * FROM t1 WHERE a = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+SELECT * FROM t1 WHERE a = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+SELECT * FROM t1 WHERE a = 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
+SELECT * FROM t1 WHERE a = 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+SELECT * FROM t1 WHERE a = 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7;
+SELECT * FROM t1 WHERE a >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
+SELECT * FROM t1 WHERE a >= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
+SELECT * FROM t1 WHERE a >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+SELECT * FROM t1 WHERE a >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+SELECT * FROM t1 WHERE a >= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
+SELECT * FROM t1 WHERE a >= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+SELECT * FROM t1 WHERE a >= 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
+SELECT * FROM t1 WHERE a > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
+SELECT * FROM t1 WHERE a > 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
+SELECT * FROM t1 WHERE a > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+SELECT * FROM t1 WHERE a > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+SELECT * FROM t1 WHERE a > 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
+SELECT * FROM t1 WHERE a > 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+SELECT * FROM t1 WHERE a > 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
+
+SELECT * FROM t1 WHERE a < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
+SELECT * FROM t1 WHERE a < 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+SELECT * FROM t1 WHERE a < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+SELECT * FROM t1 WHERE a < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+SELECT * FROM t1 WHERE a < 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+SELECT * FROM t1 WHERE a < 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+SELECT * FROM t1 WHERE a <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+SELECT * FROM t1 WHERE a <= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
+SELECT * FROM t1 WHERE a <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+SELECT * FROM t1 WHERE a <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+SELECT * FROM t1 WHERE a <= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
+SELECT * FROM t1 WHERE a <= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+SELECT * FROM t1 WHERE a = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
+SELECT * FROM t1 WHERE a = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+SELECT * FROM t1 WHERE a = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+SELECT * FROM t1 WHERE a = 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
+SELECT * FROM t1 WHERE a = 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+SELECT * FROM t1 WHERE a >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
+SELECT * FROM t1 WHERE a >= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
+SELECT * FROM t1 WHERE a >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+SELECT * FROM t1 WHERE a >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+SELECT * FROM t1 WHERE a >= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
+SELECT * FROM t1 WHERE a >= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+SELECT * FROM t1 WHERE a > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
+SELECT * FROM t1 WHERE a > 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
+SELECT * FROM t1 WHERE a > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+SELECT * FROM t1 WHERE a > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+SELECT * FROM t1 WHERE a > 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
+SELECT * FROM t1 WHERE a > 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+DROP TABLE t1;
+
 #
 # Bug#20577: Partitions: use of to_days() function leads to selection failures
 #