revision-id: 78f72d4c7984230288f42ba1cb397125f8862866 (mariadb-10.2.31-642-g78f72d4c798) parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598 author: Varun Gupta committer: Varun Gupta timestamp: 2020-12-31 12:21:08 +0530 message: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins For IN/ALL/ANY/SOME subqueries with window functions, IN to EXISTS strategy cannot be used because with IN->EXISTS transformation the window function would be pushed inside the HAVING clause which is not correct. So disabling the queries where IN->EXISTS transformation is used with window functions. --- mysql-test/r/win.result | 26 ++++++++++++++++++++++++-- mysql-test/t/win.test | 20 ++++++++++++++++++++ sql/item_subselect.cc | 10 ++++++++++ sql/opt_subselect.cc | 10 +++++++++- 4 files changed, 63 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 3023a86eaad..9bce517a108 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3153,8 +3153,7 @@ DROP TABLE t1; CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); -('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1) -0 +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' DROP TABLE t1; # # MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function @@ -3866,5 +3865,28 @@ NULL DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +# failed in setup_jtbm_semi_joins +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +a b +2 2 +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +a b +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +a b +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +a b +1 1 +2 2 +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +select a from t1 where a in (select row_number() over (order by a) from t1 A +union select row_number() over (order by a) from t1 B) ; +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c7e3dac598b..869ecd756f5 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1941,6 +1941,7 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); +--error ER_NOT_SUPPORTED_YET SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); DROP TABLE t1; @@ -2522,6 +2523,25 @@ SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1; DROP VIEW v1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +--echo # failed in setup_jtbm_semi_joins +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +--error ER_NOT_SUPPORTED_YET +select a from t1 where a in (select row_number() over (order by a) from t1 A + union select row_number() over (order by a) from t1 B) ; + +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 802bfca64b7..018c4246619 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3211,6 +3211,16 @@ Item_in_subselect::select_in_like_transformer(JOIN *join) { if (sl->join) { + for (ORDER* order= sl->join->order; order; order= order->next) + { + if (order->item[0]->with_window_func) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "the combination of this ALL/ANY/SOME/IN subquery with this" + " comparison operator and with contained window functions"); + DBUG_RETURN(TRUE); + } + } sl->join->order= 0; sl->join->skip_sort_order= 1; } diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fec4e8b2828..54a65353756 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5574,7 +5574,8 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, subq_pred->jtbm_record_count=rows; JOIN *subq_join= subq_pred->unit->first_select()->join; - if (!subq_join->tables_list || !subq_join->table_count) + if ((!subq_join->tables_list || !subq_join->table_count) && + !subq_join->select_lex->have_window_funcs()) { /* A special case; subquery's join is degenerate, and it either produces @@ -5967,6 +5968,13 @@ bool JOIN::choose_subquery_plan(table_map join_tables) } else if (in_subs->test_strategy(SUBS_IN_TO_EXISTS)) { + if (select_lex->have_window_funcs()) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "the combination of this ALL/ANY/SOME/IN subquery with this" + " comparison operator and with contained window functions"); + return true; + } if (reopt_result == REOPT_NONE && in_to_exists_where && const_tables != table_count) {