From eded60737d6bd233c50c4f68e76432154f768363 Mon Sep 17 00:00:00 2001 From: Martin Hansson Date: Fri, 9 Oct 2009 11:30:40 +0200 Subject: Bug#42846: wrong result returned for range scan when using covering index When two range predicates were combined under an OR predicate, the algorithm tried to merge overlapping ranges into one. But the case when a range overlapped several other ranges was not handled. This lead to 1) ranges overlapping, which gave repeated results and 2) a range that overlapped several other ranges was cut off. Fixed by 1) Making sure that a range got an upper bound equal to the next range with a greater minimum. 2) Removing a continue statement --- mysql-test/t/range.test | 125 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 125 insertions(+) (limited to 'mysql-test/t/range.test') 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; -- cgit v1.2.1