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@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: