revision-id: ec3f22d8c86fa0b53b4439551aeca453d7ec54b9 (mariadb-10.2.14-52-gec3f22d) parent(s): e34d3184fd02967616bb83904aa3c21977ce6205 author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-17 10:34:04 -0700 message: MDEV-15894 Error, while using aggregated functions/window functions in anchor part Usage of aggregate/window functions in non-recursive parts of recursive CTEs is allowed. Error messages complaining about this was a mistake.. --- mysql-test/r/cte_recursive.result | 19 +++++++++++++++++++ mysql-test/t/cte_recursive.test | 19 +++++++++++++++++++ sql/sql_cte.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index e1a52be..6b2db35 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3179,3 +3179,22 @@ p1 k2 p2 k1 10 10 10 10 DROP PROCEDURE getNums; DROP TABLE t1; +# +# MDEV-15894: aggregate/winfow functions in non-recorsive part +# +create table t1(b int); +insert into t1 values(10),(20),(10); +with recursive qn as +(select max(b) as a from t1 union +select a from qn) +select * from qn; +a +20 +with recursive qn as +(select rank() over (order by b) as a from t1 union +select a from qn) +select * from qn; +a +1 +3 +drop table t1; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index e3032fc..50cb39a 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2190,3 +2190,22 @@ call getNums(); DROP PROCEDURE getNums; DROP TABLE t1; + +--echo # +--echo # MDEV-15894: aggregate/winfow functions in non-recorsive part +--echo # + +create table t1(b int); +insert into t1 values(10),(20),(10); + +with recursive qn as + (select max(b) as a from t1 union + select a from qn) +select * from qn; + +with recursive qn as + (select rank() over (order by b) as a from t1 union + select a from qn) +select * from qn; + +drop table t1; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 6bc833b..cd2516c 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1173,7 +1173,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) /* Check conditions 3-4 for restricted specification*/ - if (with_sum_func || + if ((with_sum_func && !with_elem->is_anchor(this)) || (with_elem->contains_sq_with_recursive_reference())) with_elem->get_owner()->add_unrestricted( with_elem->get_mutually_recursive());