From ab5dc625458c2fd63489292117d4420610a79a9d Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 13 Apr 2021 20:32:16 +0300 Subject: MDEV-25407: EXISTS subquery with correlation in ON expression crashes Make Item_subselect::walk() walk the ON expressions, too. --- mysql-test/r/subselect_exists2in.result | 18 ++++++++++++++++++ mysql-test/t/subselect_exists2in.test | 23 +++++++++++++++++++++++ sql/item_subselect.cc | 30 +++++++++++++++++++++++++++++- 3 files changed, 70 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index de4e428e462..ff5605575ba 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -1099,4 +1099,22 @@ U5.`storage_target_id` = V0.`id` ); id drop table t1,t2,t3; +# +# MDEV-25407: EXISTS subquery with correlation in ON expression crashes +# +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; +create table t1 (a int, b int); +insert into t1 select a,a from t10; +create table t2 (a int, b int); +insert into t2 select a,a from t11; +create table t3 as select * from t2; +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +1 PRIMARY t2 ALL NULL NULL NULL NULL 1000 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 1000 Using where; End temporary; Using join buffer (incremental, BNL join) +drop table t1, t2, t3, t10, t11; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index e27ce57038b..e70d643138b 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -941,5 +941,28 @@ WHERE ( drop table t1,t2,t3; +--echo # +--echo # MDEV-25407: EXISTS subquery with correlation in ON expression crashes +--echo # +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; + +create table t1 (a int, b int); +insert into t1 select a,a from t10; + +create table t2 (a int, b int); +insert into t2 select a,a from t11; + +create table t3 as select * from t2; + + +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); + +drop table t1, t2, t3, t10, t11; + + #restore defaults set optimizer_switch=default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 4b8f118ca43..ed8e5e900a2 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -664,6 +664,31 @@ bool Item_subselect::is_expensive() } +static +int walk_items_for_table_list(Item_processor processor, + bool walk_subquery, void *argument, + List& join_list) +{ + List_iterator li(join_list); + int res; + while (TABLE_LIST *table= li++) + { + if (table->on_expr) + { + if ((res= table->on_expr->walk(processor, walk_subquery, argument))) + return res; + } + if (table->nested_join) + { + if ((res= walk_items_for_table_list(processor, walk_subquery, argument, + table->nested_join->join_list))) + return res; + } + } + return 0; +} + + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, void *argument) { @@ -695,7 +720,10 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, if (lex->having && (lex->having)->walk(processor, walk_subquery, argument)) return 1; - /* TODO: why does this walk WHERE/HAVING but not ON expressions of outer joins? */ + + if (walk_items_for_table_list(processor, walk_subquery, argument, + *lex->join_list)) + return 1; while ((item=li++)) { -- cgit v1.2.1