[Commits] ec3f22d: MDEV-15894 Error, while using aggregated functions/window functions in anchor part

IgorBabaev igor at mariadb.com
Tue Apr 17 20:34:04 EEST 2018


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());


More information about the commits mailing list