Changeset: 3aba83912acd for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/3aba83912acd
Added Files:
sql/test/BugTracker-2021/Tests/plan-not-optimal-view.Bug-7140.test
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_rel.h
sql/server/rel_unnest.c
sql/test/BugTracker-2021/Tests/All
Branch: Jul2021
Log Message:
Added test and fix for bug #7140, ie allow pushdown of select expressions under
projections with self references
diffs (282 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -2894,7 +2894,7 @@ rel_merge_projects(visitor *v, sql_rel *
prj && prj->op == op_project && !(rel_is_ref(prj)) && !prj->r) {
int all = 1;
- if (project_unsafe(rel,0) || project_unsafe(prj,0) ||
exps_share_expensive_exp(rel->exps, prj->exps))
+ if (project_unsafe(rel,0,0) || project_unsafe(prj,0,0) ||
exps_share_expensive_exp(rel->exps, prj->exps))
return rel;
/* here we need to fix aliases */
@@ -3396,8 +3396,8 @@ rel_merge_union(visitor *v, sql_rel *rel
sql_rel *ref = NULL;
if (is_union(rel->op) &&
- l && is_project(l->op) && !project_unsafe(l,0) &&
- r && is_project(r->op) && !project_unsafe(r,0) &&
+ l && is_project(l->op) && !project_unsafe(l,0,0) &&
+ r && is_project(r->op) && !project_unsafe(r,0,0) &&
(ref = rel_find_ref(l)) != NULL && ref == rel_find_ref(r)) {
/* Find selects and try to merge */
sql_rel *ls = rel_find_select(l);
@@ -4687,7 +4687,7 @@ rel_push_select_down(visitor *v, sql_rel
if (is_select(rel->op) && r && r->op == op_project && !(rel_is_ref(r))){
sql_rel *pl = r->l;
/* we cannot push through rank (row_number etc) functions or
projects with distinct */
- if (pl && !project_unsafe(r, 1)) {
+ if (pl && !project_unsafe(r, 1, 1)) {
/* introduce selects under the project (if needed) */
set_processed(pl);
for (n = exps->h; n;) {
@@ -5619,7 +5619,7 @@ rel_push_project_down_union(visitor *v,
sql_rel *ul = u->l;
sql_rel *ur = u->r;
- if (!u || !is_union(u->op) || need_distinct(u) || !u->exps ||
rel_is_ref(u) || project_unsafe(rel,0))
+ if (!u || !is_union(u->op) || need_distinct(u) || !u->exps ||
rel_is_ref(u) || project_unsafe(rel,0,0))
return rel;
/* don't push project down union of single values */
if ((is_project(ul->op) && !ul->l) || (is_project(ur->op) &&
!ur->l))
@@ -6427,8 +6427,8 @@ rel_push_project_up(visitor *v, sql_rel
(is_left(rel->op) && (rel->flag&MERGE_LEFT) /* can't push
projections above merge statments left joins */) ||
(is_select(rel->op) && l->op != op_project) ||
(is_join(rel->op) && ((l->op != op_project && r->op !=
op_project) || is_topn(r->op) || is_sample(r->op))) ||
- ((l->op == op_project && (!l->l || l->r ||
project_unsafe(l,is_select(rel->op)))) ||
- (is_join(rel->op) && (r->op == op_project && (!r->l || r->r
|| project_unsafe(r,0))))))
+ ((l->op == op_project && (!l->l || l->r ||
project_unsafe(l,is_select(rel->op),0))) ||
+ (is_join(rel->op) && (r->op == op_project && (!r->l || r->r
|| project_unsafe(r,0,0))))))
return rel;
if (l->op == op_project && l->l) {
@@ -6754,7 +6754,7 @@ rel_exps_mark_used(sql_allocator *sa, sq
}
/* for count/rank we need atleast one column */
if (!nr && subrel && (is_project(subrel->op) || is_base(subrel->op)) &&
!list_empty(subrel->exps) &&
- (is_simple_project(rel->op) && project_unsafe(rel, 0))) {
+ (is_simple_project(rel->op) && project_unsafe(rel, 0, 0))) {
sql_exp *e = subrel->exps->h->data;
e->used = 1;
}
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -25,7 +25,7 @@ rel_set_exps(sql_rel *rel, list *exps)
/* some projections results are order dependend (row_number etc) */
int
-project_unsafe(sql_rel *rel, int allow_identity)
+project_unsafe(sql_rel *rel, int allow_identity, int allow_self_reference)
{
sql_rel *sub = rel->l;
node *n;
@@ -43,9 +43,8 @@ project_unsafe(sql_rel *rel, int allow_i
/* aggr func in project ! */
if (exp_unsafe(e, allow_identity))
return 1;
- ne = rel_find_exp(rel, e);
- if (ne && ne != e) /* no self referencing */
- return 1;
+ if (!allow_self_reference && (ne = rel_find_exp(rel, e)) && ne
!= e)
+ return 1; /* no self referencing */
}
return 0;
}
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -52,7 +52,7 @@
#define is_sql_merge(X) ((X & sql_merge) == sql_merge)
extern void rel_set_exps(sql_rel *rel, list *exps);
-extern int project_unsafe(sql_rel *rel, int allow_identity);
+extern int project_unsafe(sql_rel *rel, int allow_identity, int
allow_self_reference);
extern const char *rel_name( sql_rel *r );
extern sql_rel *rel_distinct(sql_rel *l);
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
@@ -3296,7 +3296,7 @@ rewrite_remove_xp_project(visitor *v, sq
if (rel->op == op_join && list_empty(rel->exps)) {
sql_rel *r = rel->r;
- if (is_simple_project(r->op) && r->l && !project_unsafe(r, 1)) {
+ if (is_simple_project(r->op) && r->l && !project_unsafe(r, 1,
0)) {
sql_rel *rl = r->l;
if (is_simple_project(rl->op) && !rl->l &&
list_length(rl->exps) == 1) {
diff --git a/sql/test/BugTracker-2021/Tests/All
b/sql/test/BugTracker-2021/Tests/All
--- a/sql/test/BugTracker-2021/Tests/All
+++ b/sql/test/BugTracker-2021/Tests/All
@@ -16,6 +16,7 @@ WITH-alias-DELETE-1.deletes-wrong-tuples
WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133
merge-delete.Bug-7136
HAVE_LIBPY3?python-aggregates-void-bat.Bug-7138
+plan-not-optimal-view.Bug-7140
count-distinct.Bug-7141
HAVE_LIBPY3?aggregates-tables.Bug-7142
type-upcasting-INT2BIGINT.Bug-7144
diff --git a/sql/test/BugTracker-2021/Tests/plan-not-optimal-view.Bug-7140.test
b/sql/test/BugTracker-2021/Tests/plan-not-optimal-view.Bug-7140.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2021/Tests/plan-not-optimal-view.Bug-7140.test
@@ -0,0 +1,157 @@
+statement ok
+START TRANSACTION
+
+statement ok
+create table "plantest0" ("id" bigint)
+
+statement ok
+create table "plantest1" ("id" bigint)
+
+statement ok
+create procedure plantestp()
+begin
+declare rowindex bigint;
+set rowindex = 0;
+while rowindex < 5 do
+insert into plantest0 (id) values (100000000 + rowindex);
+insert into plantest1 (id) values (110000000 + rowindex);
+set rowindex = rowindex + 1;
+end while;
+end
+
+statement ok
+call plantestp()
+
+statement ok
+create view plantestv as
+select
+v.*, v.id / 10000000 as id_div
+from
+(
+select * from plantest0 union all
+select * from plantest1
+) as v
+
+query T nosort
+plan select
+id_r * 10000000 as id_range_base,
+count(id_r) as nrows
+from
+(select
+id / 10000000
+from
+plantestv v
+where
+id >= 150000000
+) as t (id_r)
+group by
+id_r
+order by
+id_r asc
+----
+project (
+| project (
+| | group by (
+| | | union (
+| | | | group by (
+| | | | | project (
+| | | | | | select (
+| | | | | | | table("sys"."plantest0") [ "plantest0"."id" ] COUNT
+| | | | | | ) [ "plantest0"."id" >= bigint "150000000" ]
+| | | | | ) [ "sys"."sql_div"("plantest0"."id" as "v"."id", int "10000000") as
"t"."id_r" ]
+| | | | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."count" no nil ("t"."id_r") NOT
NULL as "%6"."%6" ],
+| | | | group by (
+| | | | | project (
+| | | | | | select (
+| | | | | | | table("sys"."plantest1") [ "plantest1"."id" ] COUNT
+| | | | | | ) [ "plantest1"."id" >= bigint "150000000" ]
+| | | | | ) [ "sys"."sql_div"("plantest1"."id" as "v"."id", int "10000000") as
"t"."id_r" ]
+| | | | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."count" no nil ("t"."id_r") NOT
NULL as "%6"."%6" ]
+| | | ) [ "t"."id_r", "%6"."%6" ]
+| | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."sum" no nil ("%6"."%6") as "%6"."%6"
]
+| ) [ "sys"."sql_mul"("t"."id_r", int "10000000") as "id_range_base",
"%6"."%6" NOT NULL as "nrows", "t"."id_r" ]
+) [ "id_range_base", "nrows" NOT NULL ] [ "t"."id_r" ASC ]
+
+query II rowsort
+select
+id_r * 10000000 as id_range_base,
+count(id_r) as nrows
+from
+(select
+id / 10000000
+from
+plantestv v
+where
+id >= 150000000
+) as t (id_r)
+group by
+id_r
+order by
+id_r asc
+----
+
+
+query T nosort
+plan select
+id_r * 10000000 as id_range_base,
+count(id_r) as nrows
+from
+(select
+id_div
+from
+plantestv v
+where
+id >= 150000000
+) as t (id_r)
+group by
+id_r
+order by
+id_r asc
+----
+project (
+| project (
+| | group by (
+| | | union (
+| | | | group by (
+| | | | | project (
+| | | | | | project (
+| | | | | | | select (
+| | | | | | | | table("sys"."plantest0") [ "plantest0"."id" ] COUNT
+| | | | | | | ) [ "plantest0"."id" >= bigint "150000000" ]
+| | | | | | ) [ "sys"."sql_div"("plantest0"."id" as "v"."id", int "10000000")
as "v"."id_div" ]
+| | | | | ) [ "v"."id_div" as "t"."id_r" ]
+| | | | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."count" no nil ("t"."id_r") NOT
NULL as "%6"."%6" ],
+| | | | group by (
+| | | | | project (
+| | | | | | project (
+| | | | | | | select (
+| | | | | | | | table("sys"."plantest1") [ "plantest1"."id" ] COUNT
+| | | | | | | ) [ "plantest1"."id" >= bigint "150000000" ]
+| | | | | | ) [ "sys"."sql_div"("plantest1"."id" as "v"."id", int "10000000")
as "v"."id_div" ]
+| | | | | ) [ "v"."id_div" as "t"."id_r" ]
+| | | | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."count" no nil ("t"."id_r") NOT
NULL as "%6"."%6" ]
+| | | ) [ "t"."id_r", "%6"."%6" ]
+| | ) [ "t"."id_r" ] [ "t"."id_r", "sys"."sum" no nil ("%6"."%6") as "%6"."%6"
]
+| ) [ "sys"."sql_mul"("t"."id_r", int "10000000") as "id_range_base",
"%6"."%6" NOT NULL as "nrows", "t"."id_r" ]
+) [ "id_range_base", "nrows" NOT NULL ] [ "t"."id_r" ASC ]
+
+query II rowsort
+select
+id_r * 10000000 as id_range_base,
+count(id_r) as nrows
+from
+(select
+id_div
+from
+plantestv v
+where
+id >= 150000000
+) as t (id_r)
+group by
+id_r
+order by
+id_r asc
+----
+
+statement ok
+ROLLBACK
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
