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 <sanja@mariadb.com>

4 files changed, 81 insertions, 0 deletions

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
           {