[Commits] 114e18b: MDEV-26470 "No database" selected when using CTE in a subquery of DELETE statement

IgorBabaev igor at mariadb.com
Sun Nov 21 07:35:54 EET 2021


revision-id: 114e18b8b68a00b3829ac231cc8f84187f529287 (mariadb-10.2.31-1263-g114e18b)
parent(s): 0dae41637abd24c8f08e925ef00490e949ce581d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-11-20 21:35:54 -0800
message:

MDEV-26470 "No database" selected when using CTE in a subquery of DELETE statement

This bug led to reporting bogus messages "No database selected" for DELETE
statements if they used subqueries in their WHERE conditions and these
subqueries contained references to CTEs.
The bug happened because the grammar rule for DELETE statement did not
call the function LEX::check_cte_dependencies_and_resolve_references() and
as a result of it references to CTEs were not identified as such.

Approved by Oleksandr Byelkin <sanja at mariadb.com>

---
 mysql-test/r/cte_nonrecursive.result | 22 ++++++++++++++++++++++
 mysql-test/t/cte_nonrecursive.test   | 23 +++++++++++++++++++++++
 sql/sql_yacc.yy                      |  4 ++++
 3 files changed, 49 insertions(+)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index d9b7f05..b2fee0d 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -2162,4 +2162,26 @@ a
 1
 3
 drop table t1,t2;
+#
+# MDEV-26470: CTE in WITH clause of subquery used in DELETE
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (5);
+create table t2 (b int);
+insert into t2 values (4), (1), (3), (2);
+delete from t1
+where a in (with cte(a) as (select * from t2 where b <=2) select a from cte);
+select * from t1;
+a
+3
+7
+5
+insert into t1 values (1), (3);
+delete t1 from t1, t2
+where t1.a=t2.b or
+t1.a in (with cte(a) as (select b+1 from t2) select * from cte);
+select * from t1;
+a
+7
+drop table t1,t2;
 # End of 10.2 tests
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 4618e02..175be8b 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1601,4 +1601,27 @@ select * from t2;
 
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-26470: CTE in WITH clause of subquery used in DELETE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1), (5);
+
+create table t2 (b int);
+insert into t2 values (4), (1), (3), (2);
+
+delete from t1
+  where a in (with cte(a) as (select * from t2 where b <=2) select a from cte);
+select * from t1;
+
+insert into t1 values (1), (3);
+
+delete t1 from t1, t2
+  where t1.a=t2.b or
+        t1.a in (with cte(a) as (select b+1 from t2) select * from cte);
+select * from t1;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 4dd2922..a4b1058 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -12668,6 +12668,10 @@ delete:
             lex->select_lex.init_order();
           }
           opt_delete_options single_multi
+          {
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
+          }
         ;
 
 single_multi:


More information about the commits mailing list