revision-id: e00bafaa6162ff8967a0d8eed6aa12806cfd4552 (mariadb-10.1.43-248-ge00bafaa616) parent(s): e3c18b8e849373821b9c009b285ae13ef0fcc1a8 author: Varun Gupta committer: Varun Gupta timestamp: 2020-08-05 04:16:24 +0530 message: MDEV-9513: Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index Removing the ORDER BY clause from the UNION when UNION is inside an IN/ALL/ANY subquery. The rewrites are done for subqueries but this rewrite is not done for the fake_select of the UNION. --- mysql-test/r/subselect4.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 20 ++++++++++++++++++++ sql/sql_union.cc | 16 ++++++++++++++++ 3 files changed, 66 insertions(+) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 606ab847028..81cb075a891 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2608,3 +2608,33 @@ region area population Central America and the Caribbean 442 66422 SET @@optimizer_switch= @save_optimizer_switch; DROP TABLE t1; +# +# MDEV-9513: Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (2),(3); +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +a +1 +2 +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +a +1 +2 +DROP TABLE t1,t2; +# end of 10.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 21ec28b1c03..3dc27d349af 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2138,3 +2138,23 @@ WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = SET @@optimizer_switch= @save_optimizer_switch; DROP TABLE t1; + +--echo # +--echo # MDEV-9513: Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (2),(3); +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); + +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +DROP TABLE t1,t2; + +--echo # end of 10.1 tests diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 38de2d592ed..7239978342e 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -388,6 +388,22 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS; is_union_select= is_union() || fake_select_lex; + /* + For the fake select we need to make sure not to compute ORDER BY if + the UNION is inside an IN/ANY/ALL subquery. + For other selects this rewriting is done inside + the function check_and_do_in_subquery_rewrites. + Example: + select * from t1 where t1.a IN (select t2.a FROM t2 ORDER BY t2.b) + we remove the ORDER BY clause here as it is unnecessary because + LIMIT is currently not supported in IN/ALL/ANY subquery. + */ + if (is_union() && item && item->is_in_predicate()) + { + global_parameters()->order_list.first= NULL; + global_parameters()->order_list.elements= 0; + } + /* Global option */ if (is_union_select)