revision-id: 0dd12b4f2a72245a0fb491685c172a7b0e48cbc5 (mariadb-5.5.63-9-g0dd12b4) parent(s): 8024f8c6b86b204c3475e562587640cf2b141683 author: Igor Babaev committer: Igor Babaev timestamp: 2019-03-14 17:41:35 -0700 message: MDEV-18896 Crash in convert_join_subqueries_to_semijoins If an IN-subquery is used in a table-less select the current code should never consider it as candidate for semi-join optimizations. Yet the function check_and_do_in_subquery_rewrites() improperly checked the property "to be a table-less select". As a result such select in IN subquery was used in INSERT .. SELECT then the IN subquery by mistake was registered as a semi-join subquery and convert_subq_to_sj() was called for it. However the code of this function does not assume that the parent select of the subquery could be a table-less select. --- mysql-test/r/subselect_sj.result | 7 +++++++ mysql-test/r/subselect_sj_jcl6.result | 7 +++++++ mysql-test/t/subselect_sj.test | 10 ++++++++++ sql/opt_subselect.cc | 2 +- 4 files changed, 25 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 519d53e..73c620b 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3178,5 +3178,12 @@ EXECUTE stmt; a drop view v3; drop table t1,t2,t3; +# +# MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT +# +create table t1 (a1 varchar(25)); +create table t2 (a2 varchar(25)) ; +insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); +drop table t1,t2; # End of 5.5 test set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 3270406..03cf368 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3192,6 +3192,13 @@ EXECUTE stmt; a drop view v3; drop table t1,t2,t3; +# +# MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT +# +create table t1 (a1 varchar(25)); +create table t2 (a2 varchar(25)) ; +insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); +drop table t1,t2; # End of 5.5 test set optimizer_switch=@subselect_sj_tmp; # diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 0a35a61..aabb21b 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2869,6 +2869,16 @@ EXECUTE stmt; drop view v3; drop table t1,t2,t3; +--echo # +--echo # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT +--echo # + +create table t1 (a1 varchar(25)); +create table t2 (a2 varchar(25)) ; +insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); + +drop table t1,t2; + --echo # End of 5.5 test # The following command must be the last one the file diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f472cf1..f82d55f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -675,7 +675,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) select_lex->outer_select()->join && // 6 parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->has_strategy() && // 8 - select_lex->outer_select()->leaf_tables.elements && // 9 + select_lex->outer_select()->table_list.first && // 9 !((join->select_options | // 10 select_lex->outer_select()->join->select_options) // 10 & SELECT_STRAIGHT_JOIN)) // 10