From c63768425b03148737ba62dda7b6948283b04c94 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 8 Feb 2023 19:24:15 -0800 Subject: MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error The parser code for single-table DELETE missed the call of the function LEX::check_main_unit_semantics(). As a result the the field nested level of SELECT_LEX structures remained set 0 for all non-top level selects. This could lead to different kind of problems. In particular this did not allow to determine properly the selects where set functions had to be aggregated when they were used in inner subqueries. Approved by Oleksandr Byelkin --- mysql-test/main/delete.result | 32 ++++++++++++++++++++++++++++++++ mysql-test/main/delete.test | 41 +++++++++++++++++++++++++++++++++++++++++ sql/sql_yacc.yy | 4 ++++ sql/sql_yacc_ora.yy | 4 ++++ 4 files changed, 81 insertions(+) diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index ed3683d52f9..7a9963abc71 100644 --- a/mysql-test/main/delete.result +++ b/mysql-test/main/delete.result @@ -525,3 +525,35 @@ DELETE v2 FROM v2; ERROR HY000: Can not delete from join view 'test.v2' DROP VIEW v2, v1; DROP TABLE t1, t2; +End of 5.5 tests +# +# MDEV-30586: DELETE with WHERE containing nested subquery +# with set function aggregated in outer subquery +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +7 +delete from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +update t1 set t1.a=t1.a+10 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +drop table t1,t2,t3; +End of 10.4 tests diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test index c82420640c2..6d898ec769d 100644 --- a/mysql-test/main/delete.test +++ b/mysql-test/main/delete.test @@ -582,3 +582,44 @@ DELETE v2 FROM v2; DROP VIEW v2, v1; DROP TABLE t1, t2; + +--echo End of 5.5 tests + +--echo # +--echo # MDEV-30586: DELETE with WHERE containing nested subquery +--echo # with set function aggregated in outer subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); + +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); + +let $c= + t1.a in (select t3.a from t3 group by t3.a + having t3.a > any (select t2.b from t2 + where t2.b*10 < sum(t3.b))); + +eval +select * from t1 + where $c; + +eval +delete from t1 + where $c; + +eval +select * from t1 + where $c; + +eval +update t1 set t1.a=t1.a+10 + where $c; + +drop table t1,t2,t3; + +--echo End of 10.4 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 0221e4ed433..902b7d16022 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -14032,6 +14032,8 @@ delete_part2: { Lex->last_table()->vers_conditions= Lex->vers_conditions; Lex->pop_select(); //main select + if (Lex->check_main_unit_semantics()) + MYSQL_YYABORT; } ; @@ -14068,6 +14070,8 @@ single_multi: if ($3) Select->order_list= *($3); Lex->pop_select(); //main select + if (Lex->check_main_unit_semantics()) + MYSQL_YYABORT; } | table_wild_list { diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index f6cda75a677..1e1af943d83 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -14162,6 +14162,8 @@ delete_part2: { Lex->last_table()->vers_conditions= Lex->vers_conditions; Lex->pop_select(); //main select + if (Lex->check_main_unit_semantics()) + MYSQL_YYABORT; } ; @@ -14198,6 +14200,8 @@ single_multi: if ($3) Select->order_list= *($3); Lex->pop_select(); //main select + if (Lex->check_main_unit_semantics()) + MYSQL_YYABORT; } | table_wild_list { -- cgit v1.2.1