[Commits] 8388a82: MDEV-27086 "No database selected" when using UNION of CTEs to define table

IgorBabaev igor at mariadb.com
Sat Nov 20 00:51:12 EET 2021


revision-id: 8388a825ccd6c5ce093c79108e1111e1139cef4a (mariadb-10.2.31-1258-g8388a82)
parent(s): 8f24f5fee267706c0a5f475140a19a9304e224b4
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-11-19 14:51:12 -0800
message:

MDEV-27086 "No database selected" when using UNION of CTEs to define table

This bug concerned only CREATE TABLE statements of the form
  CREATE TABLE <table name> AS <with clause> <union>.
For such a statement not all references to CTE used in <union> were resolved.
As a result a bogus message was reported for the first unresolved reference.
This happened because for such statements the function resolving references
to CTEs LEX::check_cte_dependencies_and_resolve_references() was called
prematurely in the parser.

Approved by Oleksandr Byelkin <sanja at mariadb.com>

---
 mysql-test/r/cte_nonrecursive.result | 26 ++++++++++++++++++++++++++
 mysql-test/t/cte_nonrecursive.test   | 23 +++++++++++++++++++++++
 sql/sql_yacc.yy                      |  8 ++++----
 3 files changed, 53 insertions(+), 4 deletions(-)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index db747a7..d9b7f05 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -2136,4 +2136,30 @@ drop procedure sp1;
 drop function g;
 drop function f;
 drop table t1;
+#
+# MDEV-27086: union using CTEs in CREATE TABLE
+#
+create or replace temporary table tmp as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from tmp;
+a
+1
+2
+create table t1 as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from t1;
+a
+1
+2
+insert into t1 values (3);
+create table t2 as
+with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2)
+select * from cte1 union select * from cte2;
+select * from t2;
+a
+1
+3
+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 2b90bab..4618e02 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1578,4 +1578,27 @@ drop function g;
 drop function f;
 drop table t1;
 
+--echo #
+--echo # MDEV-27086: union using CTEs in CREATE TABLE
+--echo #
+
+create or replace temporary table tmp as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from tmp;
+
+create table t1 as
+with cte1 as (select 1 as a), cte2 as (select 2 as a)
+select * from cte1 union select * from cte2;
+select * from t1;
+
+insert into t1 values (3);
+
+create table t2 as
+with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2)
+select * from cte1 union select * from cte2;
+select * from t2;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 312ea68..4dd2922 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4869,6 +4869,10 @@ create_like:
 opt_create_select:
           /* empty */ {}
         | opt_duplicate opt_as create_select_query_expression
+          {
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
+          }
         ;
 
 create_select_query_expression:
@@ -4877,16 +4881,12 @@ create_select_query_expression:
           { 
             Select->set_braces(0);
             Select->set_with_clause($1);
-            if (Lex->check_cte_dependencies_and_resolve_references())
-              MYSQL_YYABORT;
           }
           union_clause
         | opt_with_clause SELECT_SYM create_select_part2 
           create_select_part3_union_not_ready create_select_part4
           {
             Select->set_with_clause($1);
-            if (Lex->check_cte_dependencies_and_resolve_references())
-              MYSQL_YYABORT;
           }
         | '(' create_select_query_specification ')'
         | '(' create_select_query_specification ')'


More information about the commits mailing list