Merge from mysql-trunk-merge.

1 files changed, 125 insertions, 0 deletions

diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index e1411e7fd46..dc119b6a77e 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1046,3 +1046,128 @@ explain select * from t2 where a=1000 and b<11;
 
 drop table t1, t2;
 
+#
+# Bug#42846: wrong result returned for range scan when using covering index
+#
+CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
+
+CREATE TABLE t2( a INT, b INT, KEY( a, b ) );
+
+CREATE TABLE t3( a INT, b INT, KEY( a, b ) );
+
+INSERT INTO t1( a, b ) 
+VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7);
+
+INSERT INTO t2( a, b ) 
+VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1),
+       ( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), 
+       (11, 1), (12, 1), (13, 1), (14, 1), (15, 1),
+       (16, 1), (17, 1), (18, 1), (19, 1), (20, 1);
+
+INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1;
+INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1;
+
+# To make range scan compelling to the optimizer
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+
+INSERT INTO t3
+VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0),
+       (6, 0), (7, 0), (8, 0), (9, 0), (10, 0);
+
+# To make range scan compelling to the optimizer
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+
+
+#
+# Problem#1 Test queries. Will give missing results unless Problem#1 is fixed.
+# With one exception, they are independent of Problem#2.
+#
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR 
+5 < a AND b = 3 OR
+3 <= a;
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR 
+5 < a AND b = 3 OR
+3 <= a;
+
+# Query below: Tests both Problem#1 and Problem#2 (EXPLAIN differs as well)
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR 
+5 <= a AND b = 3 OR
+3 <= a;
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR 
+5 <= a AND b = 3 OR
+3 <= a;
+
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR 
+5 <= a AND b = 3 OR
+3 <= a;
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR 
+5 <= a AND b = 3 OR
+3 <= a;
+
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR 
+3 <= a;
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR 
+3 <= a;
+
+#
+# Problem#2 Test queries. 
+# These queries will give missing results if Problem#1 is fixed.
+# But Problem#1 also hides this bug.
+#
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR 
+a < 5 OR
+a < 10;
+
+EXPLAIN
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR 
+a < 5 OR
+a < 10;
+
+DROP TABLE t1, t2, t3;