diff options
-rw-r--r-- | mysql-test/r/select.result | 14 | ||||
-rw-r--r-- | mysql-test/t/select.test | 14 | ||||
-rw-r--r-- | sql/opt_range.cc | 2 |
3 files changed, 29 insertions, 1 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index b80ca2b195e..5511a4bb66b 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2714,3 +2714,17 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro f1 f2 1 1 drop table t1,t2; +CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); +insert into t1 values (1,0,0),(2,0,0); +CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); +insert into t2 values (1,'',''), (2,'',''); +CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); +insert into t3 values (1,1),(1,2); +explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 +where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and +t2.b like '%%' order by t2.b limit 0,1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 996d5854854..3dcc16f1625 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2248,4 +2248,18 @@ insert into t2 values(1,1); select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); drop table t1,t2; +# +# Bug #4981: 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure +# +CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); +insert into t1 values (1,0,0),(2,0,0); +CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); +insert into t2 values (1,'',''), (2,'',''); +CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); +insert into t3 values (1,1),(1,2); +# must have "range checked" for t2 +explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 + where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and + t2.b like '%%' order by t2.b limit 0,1; +DROP TABLE t1,t2,t3; # End of 4.1 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 71f937f90c6..67141aab6ce 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -64,7 +64,7 @@ public: uint8 min_flag, uint8 max_flag, uint8 maybe_flag); SEL_ARG(enum Type type_arg) :elements(1),use_count(1),left(0),next_key_part(0),color(BLACK), - type(type_arg) + type(type_arg),min_flag(0) {} inline bool is_same(SEL_ARG *arg) { |