diff options
-rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 26 | ||||
-rw-r--r-- | mysql-test/t/cte_nonrecursive.test | 23 | ||||
-rw-r--r-- | 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 db747a71ac4..d9b7f054271 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 2b90babab5b..4618e024d2b 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 312ea682975..4dd292258d3 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 ')' |