From 2e6d30072c501f30daff34c134cc4b4cba565950 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 8 Aug 2003 18:49:22 +0200 Subject: between tests added bugfixes will follow --- mysql-test/r/range.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/range.test | 17 +++++++++++++++++ 2 files changed, 45 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 26d9d7869c6..5a4d2228778 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -226,3 +226,31 @@ insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; id1 idnull drop table t1; +create table t1 (x int, y int, index(x), index(y)); +insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); +update t1 set y=x; +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 ALL x NULL NULL NULL 9 Using where +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 range x x 5 NULL 4 Using where +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 ALL x NULL NULL NULL 9 Using where +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 ALL x NULL NULL NULL 9 Range checked for each record (index map: 1) +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 ALL x NULL NULL NULL 9 Using where +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; +table type possible_keys key key_len ref rows Extra +t1 ref y y 5 const 1 Using where +t2 range x x 5 NULL 2 Using where +drop table t1; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index bb0e696ea11..521709eddee 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -183,3 +183,20 @@ insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; drop table t1; +# +# BETWEEN problems +# +create table t1 (x int, y int, index(x), index(y)); +insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); +update t1 set y=x; +# between with only one end fixed +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0; +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0; +# between with both expressions on both ends +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; +# equation propagation +explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; +explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; +drop table t1; + -- cgit v1.2.1 From a71ef9e643f21b79fa9051cc6fc8ea27705fe8cb Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 8 Aug 2003 19:13:27 +0200 Subject: between range optimization bug (between with const on one end only) --- mysql-test/r/range.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 5a4d2228778..4e0476fdacd 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -232,7 +232,7 @@ update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where -t2 ALL x NULL NULL NULL 9 Using where +t2 range x x 5 NULL 4 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where -- cgit v1.2.1 From 8d816967531e4399d7ff3c5fd1b7c2234f5801b5 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 9 Aug 2003 01:20:02 +0200 Subject: BETWEEN bug fix: non-const expressions on both ends --- mysql-test/r/range.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 4e0476fdacd..5da6f6c30e1 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -240,7 +240,7 @@ t2 range x x 5 NULL 4 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where -t2 ALL x NULL NULL NULL 9 Using where +t2 ALL x NULL NULL NULL 9 Range checked for each record (index map: 1) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where -- cgit v1.2.1