Changeset: 6fce72691f82 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/6fce72691f82
Modified Files:
sql/server/rel_optimize_proj.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/test/cte/Tests/All
sql/test/cte/Tests/recursive_cte_correlated_subquery.test
sql/test/cte/Tests/test_correlated_recursive_cte.test
Branch: recursive_cte
Log Message:
handle some recursive ctes with correlation
diffs (truncated from 578 to 300 lines):
diff --git a/sql/server/rel_optimize_proj.c b/sql/server/rel_optimize_proj.c
--- a/sql/server/rel_optimize_proj.c
+++ b/sql/server/rel_optimize_proj.c
@@ -1752,6 +1752,9 @@ rel_push_aggr_down_n_arry(visitor *v, sq
list *rgbe = NULL, *gbe = NULL, *exps = NULL;
node *n, *m;
+ if (is_recursive(u))
+ return rel;
+
// TODO why?
if (u->op == op_project && !need_distinct(u))
u = u->l;
@@ -1910,7 +1913,7 @@ rel_push_aggr_down(visitor *v, sql_rel *
if (!u || !(is_union(u->op) || is_munion(u->op)) ||
need_distinct(u) || is_single(u) || !u->exps || rel_is_ref(u))
return rel;
- if (is_munion(u->op) && !is_recursive(u))
+ if (is_munion(u->op))
return rel_push_aggr_down_n_arry(v, rel);
ul = u->l;
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -259,6 +259,45 @@ rel_subquery_optname(sql_query *query, s
return rel_table_optname(sql, sq, sn->name, refs);
}
+static void
+rel_rename(mvc *sql, sql_rel *nrel, char *rname, sql_rel *brel)
+{
+ assert(is_project(nrel->op));
+ if (brel) {
+ if (is_project(nrel->op) && nrel->exps) {
+ for (node *ne = nrel->exps->h, *be = brel->exps->h; ne
&& be; ne = ne->next, be = be->next) {
+ sql_exp *e = ne->data;
+ sql_exp *b = be->data;
+ char *name = NULL;
+
+ if (!is_intern(e)) {
+ if (!exp_name(e))
+ name = make_label(sql->sa,
++sql->label);
+ noninternexp_setname(sql, e, rname,
name);
+ set_basecol(e);
+ e->alias.label = b->alias.label;
+ }
+ }
+ }
+ list_hash_clear(nrel->exps);
+ } else if (is_project(nrel->op) && nrel->exps) {
+ node *ne = nrel->exps->h;
+
+ for (; ne; ne = ne->next) {
+ sql_exp *e = ne->data;
+ char *name = NULL;
+
+ if (!is_intern(e)) {
+ if (!exp_name(e))
+ name = make_label(sql->sa,
++sql->label);
+ noninternexp_setname(sql, e, rname, name);
+ set_basecol(e);
+ }
+ }
+ list_hash_clear(nrel->exps);
+ }
+}
+
sql_rel *
rel_with_query(sql_query *query, symbol *q )
{
@@ -275,7 +314,7 @@ rel_with_query(sql_query *query, symbol
symbol *sym = d->data.sym;
dnode *dn = sym->data.lval->h->next;
char *rname = qname_schema_object(dn->data.lval);
- sql_rel *nrel;
+ sql_rel *nrel, *base_rel = NULL;
symbol *recursive_part = NULL;
sql_rel_view *recursive_union = NULL;
int recursive_distinct = 0;
@@ -307,7 +346,8 @@ rel_with_query(sql_query *query, symbol
return sql_error(sql, 02, SQLSTATE(HY013)
MAL_MALLOC_FAIL);
}
if (recursive && recursive_part) {
- sql_rel *base_rel = nrel;
+ base_rel = nrel;
+ rel_rename(sql, base_rel, rname, base_rel);
dn->next->next->data.sym = recursive_part;
set_processed(nrel);
nrel = rel_semantic(query, sym);
@@ -352,23 +392,7 @@ rel_with_query(sql_query *query, symbol
return NULL;
}
}
- assert(is_project(nrel->op));
- if (is_project(nrel->op) && nrel->exps) {
- node *ne = nrel->exps->h;
-
- for (; ne; ne = ne->next) {
- sql_exp *e = ne->data;
- char *name = NULL;
-
- if (!is_intern(e)) {
- if (!exp_name(e))
- name = make_label(sql->sa,
++sql->label);
- noninternexp_setname(sql, e, rname,
name);
- set_basecol(e);
- }
- }
- list_hash_clear(nrel->exps);
- }
+ rel_rename(sql, nrel, rname, base_rel);
}
rel = rel_semantic(query, next);
stack_pop_frame(sql);
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -826,6 +826,19 @@ push_up_project(mvc *sql, sql_rel *rel,
{
sql_rel *r = rel->r;
+ if (rel_is_ref(r) && is_recursive(r)) {
+
+ if (is_join(rel->op) && is_dependent(rel)) {
+ sql_rel *l = r->l;
+ r->l = rel;
+ rel->r = l;
+ /* add missing expressions */
+ list *exps = rel_projections(sql, rel->l, NULL, 1, 1);
+ r->exps = list_distinct(list_merge(exps, r->exps,
(fdup)NULL), (fcmp)exp_equal, (fdup)NULL);
+ return r;
+ }
+ assert(0);
+ }
assert(is_simple_project(r->op));
if (rel_is_ref(r)) {
sql_rel *nr = rel_project(sql->sa, r->l ? rel_dup(r->l) : NULL,
exps_copy(sql, r->exps));
@@ -1579,12 +1592,18 @@ push_up_munion(mvc *sql, sql_rel *rel, l
sql_rel *d = rel->l, *s = rel->r;
int need_distinct = is_semi(rel->op) && need_distinct(d);
int len = 0, need_length_reduction = 0;
+ int rec = is_recursive(s);
/* left of rel should be a set */
list *rlist = sa_list(sql->sa);
if (d && is_distinct_set(sql, d, ad) && s && is_munion(s->op)) {
list *iu = s->l;
- for(node *n = iu->h; n; n = n->next) {
+ if (rec) {
+ sql_rel *r = iu->h->data;
+ set_recursive(r);
+ append(rlist, rel_dup(r));
+ }
+ for(node *n = rec?iu->h->next:iu->h; n; n = n->next) {
sql_rel *sl = n->data;
sl = rel_project(sql->sa, rel_dup(sl),
rel_projections(sql, sl, NULL, 1, 1));
for (node *n = sl->exps->h, *m = s->exps->h; n
&& m; n = n->next, m = m->next)
@@ -1611,7 +1630,7 @@ push_up_munion(mvc *sql, sql_rel *rel, l
}
}
- for(node *n = rlist->h; n; n = n->next) {
+ for(node *n = rec?rlist->h->next:rlist->h; n; n =
n->next) {
/* D djoin (sl setop sr) -> (D djoin sl) setop
(D djoin sr) */
sql_rel *sl = n->data;
sl = rel_crossproduct(sql->sa, rel_dup(d), sl,
rel->op);
@@ -1641,7 +1660,16 @@ push_up_munion(mvc *sql, sql_rel *rel, l
ns->exps = list_merge(sexps, ns->exps,
(fdup)NULL);
}
/* add/remove projections to inner parts of the union
(as we push a join or semijoin down) */
- for(node *n = rlist->h; n; n = n->next) {
+ if (rec) {
+ sql_rel *sl = rlist->h->data;
+ list *exps = exps_copy(sql, ad);
+ for(node *n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ set_freevar(e, 0);
+ }
+ sl->exps = list_merge(exps, sl->exps,
(fdup)NULL);
+ }
+ for(node *n = rec?rlist->h->next:rlist->h; n; n =
n->next) {
sql_rel *sl = n->data;
n->data = rel_project(sql->sa, sl,
rel_projections(sql, sl, NULL, 1, 1));
}
diff --git a/sql/test/cte/Tests/All b/sql/test/cte/Tests/All
--- a/sql/test/cte/Tests/All
+++ b/sql/test/cte/Tests/All
@@ -6,7 +6,7 @@ recursive_cte_complex_pipelines
#recursive_cte_correlated_subquery
recursive_cte_error
recursive_hang_2745
-#test_correlated_recursive_cte
+test_correlated_recursive_cte
test_cte_in_cte
test_cte_overflow
test_cte
diff --git a/sql/test/cte/Tests/recursive_cte_correlated_subquery.test
b/sql/test/cte/Tests/recursive_cte_correlated_subquery.test
--- a/sql/test/cte/Tests/recursive_cte_correlated_subquery.test
+++ b/sql/test/cte/Tests/recursive_cte_correlated_subquery.test
@@ -4,26 +4,26 @@ input(sud) AS (
VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),
digits(z, lp) AS (
-SELECT CAST(lp+1 AS TEXT), lp::int+1 FROM generate_series(0,8,1) t(lp)
+SELECT CAST(lp+1 AS TEXT), cast(lp as int)+1 FROM generate_series(0,8+1,1)
t(lp)
),
x(s, ind) AS (
-SELECT sud, instr(sud, '.') FROM input
+SELECT sud, contains(sud, '.') FROM input
UNION ALL
SELECT
-substr(s, 1, ind::int-1) || z || substr(s, ind::int+1),
-instr(substr(s, 1, ind::int-1) || z || substr(s, ind::int+1), '.' )
+substr(s, 1, cast(ind as int)-1) || z || substr(s, cast(ind as int)+1),
+contains(substr(s, 1, cast(ind as int)-1) || z || substr(s, cast(ind as
int)+1), '.' )
FROM x, digits AS z
-WHERE ind::int>0
+WHERE cast(ind as int) >0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
-WHERE z.z = substr(s, ((ind::int-1)//9)*9 + lp, 1)
-OR z.z = substr(s, ((ind::int-1)%9) + (lp-1)*9 + 1, 1)
-OR z.z = substr(s, (((ind::int-1)//3) % 3) * 3
-+ ((ind::int-1)//27) * 27 + lp
-+ ((lp-1) // 3) * 6, 1)
+WHERE z.z = substr(s, ((cast(ind as int)-1)/9)*9 + lp, 1)
+OR z.z = substr(s, ((cast(ind as int)-1)%9) + (lp-1)*9 + 1, 1)
+OR z.z = substr(s, (((cast(ind as int)-1)/3) % 3) * 3
++ ((cast(ind as int)-1)/27) * 27 + lp
++ ((lp-1) / 3) * 6, 1)
)
)
-SELECT s FROM x WHERE ind::int=0;
+SELECT s FROM x WHERE cast(ind as int)=0;
----
534678912672195348198342567859761423426853791713924856961537284287419635345286179
diff --git a/sql/test/cte/Tests/test_correlated_recursive_cte.test
b/sql/test/cte/Tests/test_correlated_recursive_cte.test
--- a/sql/test/cte/Tests/test_correlated_recursive_cte.test
+++ b/sql/test/cte/Tests/test_correlated_recursive_cte.test
@@ -12,13 +12,20 @@ FROM generate_series(1,4+1) AS _(x), L
SELECT * FROM t) AS t
ORDER BY x, y;
----
-1 1
-1 2
-1 3
-2 2
-2 3
-3 3
-4 4
+1
+1
+1
+2
+1
+3
+2
+2
+2
+3
+3
+3
+4
+4
# Correlation in the recursive query
query II
@@ -34,15 +41,24 @@ FROM generate_series(1,4+1) AS _(x), L
SELECT * FROM t) AS t
ORDER BY x, y;
----
-1 1
-1 2
-1 3
-2 1
-2 3
-3 1
-3 4
-4 1
-4 5
+1
+1
+1
+2
+1
+3
+2
+1
+2
+3
+3
+1
+3
+4
+4
+1
+4
+5
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
