diff options
| author | Igor Babaev <igor@askmonty.org> | 2010-09-26 09:12:34 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2010-09-26 09:12:34 -0700 |
| commit | 716e84164a62396041251ea6d38bcb87d82330a4 (patch) | |
| tree | d90f262b4a60f8a04c21503b410349dd7803ebb1 /mysql-test/r/join_outer.result | |
| parent | d91422f03ace92ea4a2e36e62ded348cc2e0adc8 (diff) | |
| download | mariadb-git-716e84164a62396041251ea6d38bcb87d82330a4.tar.gz | |
Fixed bug #57024.
The condition over the outer tables now are extracted from
the on condition of any outer join. This condition is
saved in a special field of the JOIN_TAB structure for
the first inner table of the outer join. The condition
is checked before the first inner table is accessed. If
it turns out to be false the table is not accessed at all
and a null complemented row is generated immediately.
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 59 |
1 files changed, 59 insertions, 0 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 8c9c121c9be..f9fb545bd0e 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -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 |