Fixed problem with range optimization over overlapping ranges (#2448)

4 files changed, 46 insertions, 13 deletions

diff --git a/mysql-test/r/ctype_tis620.result b/mysql-test/r/ctype_tis620.result
index d939fd12e60..811609d4ba9 100644
--- a/mysql-test/r/ctype_tis620.result
+++ b/mysql-test/r/ctype_tis620.result
@@ -1,17 +1,18 @@
-DROP TABLE IF EXISTS t620;
-CREATE TABLE t620 (
+drop table if exists t1;
+CREATE TABLE t1 (
 recid int(11) NOT NULL auto_increment,
 dyninfo text,
 PRIMARY KEY  (recid)
-) TYPE=MyISAM;
-INSERT INTO t620 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n');
-INSERT INTO t620 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n');
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n');
+INSERT INTO t1 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n');
 SELECT DISTINCT 
 (IF( LOCATE( 'year=\"', dyninfo ) = 1, 
 SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), 
 IF( LOCATE( '\nyear=\"', dyninfo ), 
 SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, 
 LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) AS year 
-FROM t620
+FROM t1
 HAVING year != '' ORDER BY year;
 year
+DROP TABLE t1;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index e87df9a6c24..ccfdbd62a42 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -300,3 +300,16 @@ SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
 COUNT(*)
 6
 DROP TABLE t1;
+CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
+INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
+SELECT * FROM t1
+WHERE
+(
+( b =1 AND a BETWEEN 14 AND 21 ) OR
+( b =2 AND a BETWEEN 16 AND 18 ) OR
+( b =3 AND a BETWEEN 15 AND 19 ) OR
+(a BETWEEN 19 AND 47)
+);
+a	b
+15	1
+47	1
diff --git a/mysql-test/t/ctype_tis620.test b/mysql-test/t/ctype_tis620.test
index 7a0555515e1..7314b52e301 100644
--- a/mysql-test/t/ctype_tis620.test
+++ b/mysql-test/t/ctype_tis620.test
@@ -1,12 +1,15 @@
-DROP TABLE IF EXISTS t620;
-CREATE TABLE t620 (
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+CREATE TABLE t1 (
   recid int(11) NOT NULL auto_increment,
   dyninfo text,
   PRIMARY KEY  (recid)
-) TYPE=MyISAM;
+) ENGINE=MyISAM;
 
-INSERT INTO t620 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n');
-INSERT INTO t620 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n');
+INSERT INTO t1 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n');
+INSERT INTO t1 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n');
 
 SELECT DISTINCT 
  (IF( LOCATE( 'year=\"', dyninfo ) = 1, 
@@ -14,5 +17,7 @@ SELECT DISTINCT
   IF( LOCATE( '\nyear=\"', dyninfo ), 
   SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, 
   LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) AS year 
-FROM t620
+FROM t1
 HAVING year != '' ORDER BY year;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index b04e614a740..2899c7c99a4 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -238,7 +238,7 @@ SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
 DROP TABLE t1;
 
 #
-# Test error with 
+# Test problem with range optimzer and sub ranges
 #
 
 CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
@@ -249,3 +249,17 @@ SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
 SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
 DROP TABLE t1;
 
+#
+# Test problem with range optimization over overlapping ranges (#2448)
+#
+
+CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
+INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
+SELECT * FROM t1
+WHERE
+(
+    ( b =1 AND a BETWEEN 14 AND 21 ) OR
+    ( b =2 AND a BETWEEN 16 AND 18 ) OR
+    ( b =3 AND a BETWEEN 15 AND 19 ) OR
+    (a BETWEEN 19 AND 47)
+);