summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Petrunia <psergey@askmonty.org>2020-07-24 22:31:29 +0300
committerSergei Petrunia <psergey@askmonty.org>2020-07-24 22:32:04 +0300
commitb000d6952f655f837bc8c241add196f80a997e63 (patch)
treeaf9338491d50693001e98452a56dccec54927436
parent4b97f14a3d692721a089afe8050a00fcfc230816 (diff)
downloadmariadb-git-b000d6952f655f837bc8c241add196f80a997e63.tar.gz
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
-rw-r--r--mysql-test/r/subselect_exists2in.result70
-rw-r--r--mysql-test/t/subselect_exists2in.test48
-rw-r--r--sql/item_subselect.cc20
-rw-r--r--sql/sql_lex.cc3
-rw-r--r--sql/sql_select.cc2
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,