revision-id: 86b615fb6e4841817cb2b236b0d9a7b7d2c4330e (mariadb-10.2.14-118-g86b615f) parent(s): d309c2fc885a5d50645ca9b43da2fda09f8b4418 author: Igor Babaev committer: Igor Babaev timestamp: 2018-05-17 22:56:27 -0700 message: MDEV-15581 Incorrect result (missing row) with UNION DISTINCT in anchor parts The current code does not support recursive CTEs whose specifications contain a mix of ALL UNION and DISTINCT UNION operations. This patch catches such specifications and reports errors for them. --- mysql-test/r/cte_recursive.result | 20 ++++++++++++++++++++ mysql-test/t/cte_recursive.test | 24 ++++++++++++++++++++++++ sql/sql_cte.cc | 12 ++++++++---- sql/sql_lex.cc | 1 + sql/sql_lex.h | 1 + sql/sql_union.cc | 17 +++++++++++++++++ sql/sql_yacc.yy | 1 + 7 files changed, 72 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index b7516b8..2e93e9f 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3243,3 +3243,23 @@ SELECT 1 FROM qn ORDER BY (SELECT * FROM qn)) SELECT count(*) FROM qn; ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +# +# MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE +# +create table t1(a int); +insert into t1 values(1),(2); +insert into t1 values(1),(2); +set @c=0, @d=0; +WITH RECURSIVE qn AS +( +select 1,0 as col from t1 +union distinct +select 1,0 from t1 +union all +select 3, 0*(@c:=@c+1) from qn where @c<1 +union all +select 3, 0*(@d:=@d+1) from qn where @d<1 +) +select * from qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +drop table t1; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index b327647..32a82c4 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2258,3 +2258,27 @@ SELECT 1 FROM dual UNION ALL SELECT 1 FROM qn ORDER BY (SELECT * FROM qn)) SELECT count(*) FROM qn; + +--echo # +--echo # MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE +--echo # + +create table t1(a int); +insert into t1 values(1),(2); +insert into t1 values(1),(2); + +set @c=0, @d=0; +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS +( +select 1,0 as col from t1 +union distinct +select 1,0 from t1 +union all +select 3, 0*(@c:=@c+1) from qn where @c<1 +union all +select 3, 0*(@d:=@d+1) from qn where @d<1 +) +select * from qn; + +drop table t1; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index cd2516c..fdd6943 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -665,7 +665,6 @@ void With_element::move_anchors_ahead() { st_select_lex *next_sl; st_select_lex *new_pos= spec->first_select(); - st_select_lex *UNINIT_VAR(last_sl); new_pos->linkage= UNION_TYPE; for (st_select_lex *sl= new_pos; sl; sl= next_sl) { @@ -673,6 +672,14 @@ void With_element::move_anchors_ahead() if (is_anchor(sl)) { sl->move_node(new_pos); + if (new_pos == spec->first_select()) + { + enum sub_select_type type= new_pos->linkage; + new_pos->linkage= sl->linkage; + sl->linkage= type; + new_pos->with_all_modifier= sl->with_all_modifier; + sl->with_all_modifier= false; + } new_pos= sl->next_select(); } else if (!sq_rec_ref && no_rec_ref_on_top_level()) @@ -680,10 +687,7 @@ void With_element::move_anchors_ahead() sq_rec_ref= find_first_sq_rec_ref_in_select(sl); DBUG_ASSERT(sq_rec_ref != NULL); } - last_sl= sl; } - if (spec->union_distinct) - spec->union_distinct= last_sl; first_recursive= new_pos; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 1752689..7c963be 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2171,6 +2171,7 @@ void st_select_lex::init_select() select_limit= 0; /* denotes the default limit = HA_POS_ERROR */ offset_limit= 0; /* denotes the default offset = 0 */ with_sum_func= 0; + with_all_modifier= 0; is_correlated= 0; cur_pos_in_select_list= UNDEF_POS; cond_value= having_value= Item::COND_UNDEF; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 68e1885..4acb1e4 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -907,6 +907,7 @@ class st_select_lex: public st_select_lex_node */ bool subquery_in_having; /* TRUE <=> this SELECT is correlated w.r.t. some ancestor select */ + bool with_all_modifier; /* used for selects in union */ bool is_correlated; /* This variable is required to ensure proper work of subqueries and diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 39e7bcd..13c19da 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -453,6 +453,23 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, DBUG_ASSERT(thd == thd_arg); DBUG_ASSERT(thd == current_thd); + if (is_recursive && (sl= first_sl->next_select())) + { + SELECT_LEX *next_sl; + for ( ; ; sl= next_sl) + { + next_sl= sl->next_select(); + if (!next_sl) + break; + if (next_sl->with_all_modifier != sl->with_all_modifier) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "mix of ALL and DISTINCT UNION operations in recursive CTE spec"); + DBUG_RETURN(TRUE); + } + } + } + describe= additional_options & SELECT_DESCRIBE; /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b2088b0..47e5f2f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -707,6 +707,7 @@ bool add_select_to_union_list(LEX *lex, bool is_union_distinct, return TRUE; mysql_init_select(lex); lex->current_select->linkage=UNION_TYPE; + lex->current_select->with_all_modifier= !is_union_distinct; if (is_union_distinct) /* UNION DISTINCT - remember position */ lex->current_select->master_unit()->union_distinct= lex->current_select;