diff options
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 65 |
1 files changed, 62 insertions, 3 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 6d867f96034..aa5baeb6cc7 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1126,7 +1126,7 @@ a b a b 7 8 7 5 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); id select_type table type possible_keys key key_len ref rows Extra @@ -1335,7 +1335,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tt6 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE tt7 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE tt8 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer +1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SET optimizer_search_depth = DEFAULT; DROP TABLE t1; # @@ -1354,7 +1354,7 @@ EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where -1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer +1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 SIMPLE TA1 ALL NULL NULL NULL NULL 2 Using where DROP TABLE t1, t2; # @@ -1449,4 +1449,63 @@ group by t2.pk; pk t 2001 3001 drop table t1,t2,t3,t4; +# +# Bug#57024: Poor performance when conjunctive condition over the outer +# table is used in the on condition of an outer join +# +create table t1 (a int); +insert into t1 values (NULL), (NULL), (NULL), (NULL); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 values (4), (2), (1), (3); +create table t2 like t1; +insert into t2 select if(t1.a is null, 10, t1.a) from t1; +create table t3 (a int, b int, index idx(a)); +insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +flush status; +select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null; +sum(t3.b) +1006 +show status like "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 4 +Handler_read_next 5 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1048581 +flush status; +select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10; +sum(t3.b) +1006 +show status like "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 4 +Handler_read_next 5 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1048581 +drop table t1,t2,t3; End of 5.1 tests |