revision-id: b000d6952f655f837bc8c241add196f80a997e63 (mariadb-10.1.43-225-gb000d6952f6) parent(s): 4b97f14a3d692721a089afe8050a00fcfc230816 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-07-24 22:32:04 +0300 message: MDEV-23221: A subquery causes crash * Fix the crash: IN-to-EXISTS rewrite causes an error (and so JOIN::optimize() fails with an error, too), don't call update_used_tables(). Terminate the query execution instead. * Fix the cause of the error in the IN-to-EXISTS rewrite: don't do the rewrite if doing it will cause an error of this kind: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' * Fix another issue exposed by this testcase: JOIN::setup_subquery_caches() may be invoked before any select has saved its query plan, and will crash because none of the SELECTs has called create_explain_query_if_not_exists() to create the Explain Data Structure for this SELECT. TODO: When merging this to 10.2, remove the poorly-placed call to create_explain_query_if_not_exists made by fix for M_D_E_V-16153 --- mysql-test/r/subselect_exists2in.result | 70 +++++++++++++++++++++++++++++++++ mysql-test/t/subselect_exists2in.test | 48 ++++++++++++++++++++++ sql/item_subselect.cc | 20 +++++++++- sql/sql_lex.cc | 3 +- sql/sql_select.cc | 2 +- 5 files changed, 139 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index 18cd0567989..5a66aa2cd02 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -972,4 +972,74 @@ id DROP PROCEDURE p1; DROP TABLE t1; # End of 10.0 tests +# +# MDEV-23221: A subquery causes crash +# +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); +# Must not crash or return an error: +select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +prepare s from "select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1"; +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +drop table t1,t2; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 5a8ddb3612f..877475f97ed 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -828,5 +828,53 @@ DROP TABLE t1; --echo # End of 10.0 tests +--echo # +--echo # MDEV-23221: A subquery causes crash +--echo # +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); + +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); + +let $query= +select + (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, + (select + max(container_id) + from t2 as cl2 + where + cl2.container_id = cl1.container_id and + cl2.cntr_activity_type = 'CUV' and + exists (select location_code + from t1 + where + location_code = cl2.cntr_dest and + country_id = dest_cntry) + ) as CUV +from + t2 cl1; + +--echo # Must not crash or return an error: +eval $query; + +eval prepare s from "$query"; +execute s; +execute s; + +drop table t1,t2; + #restore defaults set optimizer_switch=default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index b5f3cd052e2..0b28c15027d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2727,6 +2727,8 @@ bool Item_exists_subselect::select_prepare_to_be_in() Check if 'func' is an equality in form "inner_table.column = outer_expr" @param func Expression to check + @param allow_subselect If true, the outer_expr part can have a subquery + If false, it cannot. @param local_field OUT Return "inner_table.column" here @param outer_expr OUT Return outer_expr here @@ -2734,6 +2736,7 @@ bool Item_exists_subselect::select_prepare_to_be_in() */ static bool check_equality_for_exist2in(Item_func *func, + bool allow_subselect, Item_ident **local_field, Item **outer_exp) { @@ -2744,7 +2747,8 @@ static bool check_equality_for_exist2in(Item_func *func, args= func->arguments(); if (args[0]->real_type() == Item::FIELD_ITEM && args[0]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[1]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[1]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[1]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[0]->type() == Item::FIELD_ITEM || @@ -2755,7 +2759,8 @@ static bool check_equality_for_exist2in(Item_func *func, } else if (args[1]->real_type() == Item::FIELD_ITEM && args[1]->all_used_tables() != OUTER_REF_TABLE_BIT && - args[0]->all_used_tables() == OUTER_REF_TABLE_BIT) + args[0]->all_used_tables() == OUTER_REF_TABLE_BIT && + (allow_subselect || !args[0]->has_subquery())) { /* It is Item_field or Item_direct_view_ref) */ DBUG_ASSERT(args[1]->type() == Item::FIELD_ITEM || @@ -2784,6 +2789,13 @@ typedef struct st_eq_field_outer outer1=inner_tbl1.col1 AND ... AND outer2=inner_tbl1.col2 AND remainder_cond + if there is just one outer_expr=inner_expr pair, then outer_expr can have a + subselect in it. If there are many such pairs, then none of outer_expr can + have a subselect in it. If we allow this, the query will fail with an error: + + This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left + expression of IN/ALL/ANY' + @param conds Condition to be checked @parm result Array to collect EQ_FIELD_OUTER elements describing inner-vs-outer equalities the function has found. @@ -2801,14 +2813,17 @@ static bool find_inner_outer_equalities(Item **conds, { List_iterator<Item> li(*((Item_cond*)*conds)->argument_list()); Item *item; + bool allow_subselect= true; while ((item= li++)) { if (item->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)item, + allow_subselect, &element.local_field, &element.outer_exp)) { found= TRUE; + allow_subselect= false; element.eq_ref= li.ref(); if (result.append(element)) goto alloc_err; @@ -2817,6 +2832,7 @@ static bool find_inner_outer_equalities(Item **conds, } else if ((*conds)->type() == Item::FUNC_ITEM && check_equality_for_exist2in((Item_func *)*conds, + true, &element.local_field, &element.outer_exp)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 02c0df97cf3..e863308159e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3802,7 +3802,8 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) sl->options|= SELECT_DESCRIBE; inner_join->select_options|= SELECT_DESCRIBE; } - res= inner_join->optimize(); + if ((res= inner_join->optimize())) + return TRUE; if (!inner_join->cleaned) sl->update_used_tables(); sl->update_correlated_cache(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 647dee80188..4c6e87e4f27 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1055,11 +1055,11 @@ int JOIN::optimize() if (optimization_state != JOIN::NOT_OPTIMIZED) return FALSE; optimization_state= JOIN::OPTIMIZATION_IN_PROGRESS; + create_explain_query_if_not_exists(thd->lex, thd->mem_root); int res= optimize_inner(); if (!res && have_query_plan != QEP_DELETED) { - create_explain_query_if_not_exists(thd->lex, thd->mem_root); have_query_plan= QEP_AVAILABLE; save_explain_data(thd->lex->explain, false /* can overwrite */, need_tmp,