diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 41 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 38 | ||||
| -rw-r--r-- | sql/item.h | 1 |
3 files changed, 80 insertions, 0 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index c1cc03729d2..10425e6082a 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1411,4 +1411,45 @@ select * from t1 left join t2 on t1.b=t2.b where 1=1; a b a b 1 NULL NULL NULL drop table t1,t2; +# +# Bug#53161: outer join in the derived table is erroneously converted +# into an inner join for a query with a group by clause +# +create table t1 (pk int not null primary key, a int not null); +create table t2 like t1; +create table t3 like t1; +create table t4 (pk int not null primary key); +insert into t1 values (1000, 1), (1001, 1); +insert into t2 values (2000, 2), (2001, 2); +insert into t3 values (3000, 3), (3001, 2); +insert into t4 values (4000), (4001); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='table_elimination=off'; +explain extended +select t2.pk, +(select t3.pk +from t3 left join t4 on t4.pk=t3.pk +where t3.pk=t2.pk+1000 limit 1 ) as t +from t1,t2 +where t2.pk=t1.pk+1000 and t1.pk>1000 +group by t2.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`pk` AS `pk`,(select `test`.`t3`.`pk` from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk` +select t2.pk, +(select t3.pk +from t3 left join t4 on t4.pk=t3.pk +where t3.pk=t2.pk+1000 limit 1 ) as t +from t1,t2 +where t2.pk=t1.pk+1000 and t1.pk>1000 +group by t2.pk; +pk t +2001 3001 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3,t4; End of 5.1 tests diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 77a352fe532..5694692dd47 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -996,4 +996,42 @@ select * from t1 left join t2 on t1.b=t2.b where 1=1; drop table t1,t2; +--echo # +--echo # Bug#53161: outer join in the derived table is erroneously converted +--echo # into an inner join for a query with a group by clause +--echo # + +create table t1 (pk int not null primary key, a int not null); +create table t2 like t1; +create table t3 like t1; +create table t4 (pk int not null primary key); +insert into t1 values (1000, 1), (1001, 1); +insert into t2 values (2000, 2), (2001, 2); +insert into t3 values (3000, 3), (3001, 2); +insert into t4 values (4000), (4001); + +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='table_elimination=off'; + +explain extended +select t2.pk, + (select t3.pk + from t3 left join t4 on t4.pk=t3.pk + where t3.pk=t2.pk+1000 limit 1 ) as t + from t1,t2 + where t2.pk=t1.pk+1000 and t1.pk>1000 + group by t2.pk; + +select t2.pk, + (select t3.pk + from t3 left join t4 on t4.pk=t3.pk + where t3.pk=t2.pk+1000 limit 1 ) as t + from t1,t2 + where t2.pk=t1.pk+1000 and t1.pk>1000 + group by t2.pk; + +set @@optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3,t4; + --echo End of 5.1 tests diff --git a/sql/item.h b/sql/item.h index 05fde79ce31..d923fb932cb 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2505,6 +2505,7 @@ public: { return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT; } + table_map not_null_tables() const { return 0; } virtual Ref_Type ref_type() { return OUTER_REF; } bool check_inner_refs_processor(uchar * arg); }; |