revision-id: d5a82208f61f2f476c2667c77c9cbb74d137374b (mariadb-10.2.31-642-gd5a82208f61) parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598 author: Varun Gupta committer: Varun Gupta timestamp: 2020-12-24 19:19:43 +0530 message: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins Currently window functions do not work with IN/ALL/ANY/SOME subqueries, so throw an error when such a query is encountered --- mysql-test/r/win.result | 28 ++++++++++++++++++++++++---- mysql-test/t/win.test | 24 ++++++++++++++++++++++++ sql/item_subselect.cc | 3 +++ sql/opt_subselect.cc | 8 ++++++++ 4 files changed, 59 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 3023a86eaad..1b4b7435351 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -2391,8 +2391,7 @@ COUNT(*) OVER (PARTITION BY c) 2 2 SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); -i -1 +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, t2; # # MDEV-9976: window function without PARTITION BY and ORDER BY @@ -3153,8 +3152,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 +3864,27 @@ 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); +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 * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +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 * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +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 * 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..32eed85224b 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1440,6 +1440,7 @@ INSERT INTO t2 VALUES ('foo'),('bar'),('foo'); SELECT COUNT(*) OVER (PARTITION BY c) FROM t2; +--error ER_NOT_SUPPORTED_YET SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); DROP TABLE t1, t2; @@ -1941,6 +1942,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 +2524,28 @@ 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); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +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..5b7b24d9e21 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1951,6 +1951,9 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) */ DBUG_ASSERT(!substitution); + if (select_lex->have_window_funcs()) + DBUG_RETURN(false); + /* Check if optimization with aggregate min/max possible 1 There is no aggregate in the subquery diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fec4e8b2828..3fdba7f4520 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5940,6 +5940,14 @@ bool JOIN::choose_subquery_plan(table_map join_tables) in_subs->set_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 (in_subs->test_strategy(SUBS_MATERIALIZATION)) { /* Restore the original query plan used for materialization. */