Changeset: 69c633647ed2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/69c633647ed2
Modified Files:
sql/server/rel_optimize_proj.c
sql/test/BugTracker-2026/Tests/7966-optimize-select-distinct-from-union-queries.test
sql/test/BugTracker-2026/Tests/7968-optimize-distinct-from-q1-except-q2.test
sql/test/BugTracker-2026/Tests/7969-optimize-distinct-from-q1-intersect-q2.test
Branch: default
Log Message:
improved distinct_project2groupby optimizer, fixes issues #7966, #7968 and #7969
diffs (105 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
@@ -3817,21 +3817,31 @@ rel_distinct_project2groupby_(visitor *v
v->changes++;
}
+ list *gbe = l?l->r:NULL;
+ if (rel->op == op_project && rel->l && !rel->r /* no order by */ &&
need_distinct(rel) &&
+ (is_groupby(l->op) && l->l && gbe) && exp_match_list(rel->exps,
gbe)) {
+ set_nodistinct(rel); /* distinct is needed only once */
+ v->changes++;
+ return rel;
+ }
+ if (rel->op == op_project && rel->l && !rel->r /* no order by */ &&
need_distinct(rel) &&
+ (is_project(l->op) && l->l && (l->op != op_project || !l->r) &&
need_distinct(l)) &&
+ exp_match_list(rel->exps, l->exps)) {
+ set_nodistinct(rel); /* distinct is needed only once */
+ v->changes++;
+ return rel;
+ }
+
/* rewrite distinct project [ pk ] ( select ( table ) [ e op val ])
* into project [ pk ] ( select/semijoin ( table ) */
- sql_rel *orel = rel;
- if (rel->op == op_project && rel->l && !rel->r /* no order by */ &&
need_distinct(rel) &&
- (l->op == op_project && l->l && !l->r) &&
list_check_prop_all(rel->exps, (prop_check_func)&exp_is_useless_rename))
- rel = l;
-
if (rel->op == op_project && rel->l && !rel->r /* no order by */ &&
need_distinct(rel) &&
(l->op == op_select || l->op == op_semi) && exps_unique(v->sql,
rel, rel->exps, true) &&
(!have_semantics(l->exps) || !have_nil(rel->exps))) {
- set_nodistinct(orel);
+ set_nodistinct(rel);
v->changes++;
+ return rel;
}
- rel = orel;
/* rewrite distinct project ( join(p,f) [ p.pk = f.fk ] ) [ p.pk ]
* into project( (semi)join(p,f) [ p.pk = f.fk ] ) [ p.pk ] */
if (rel->op == op_project && rel->l && !rel->r /* no order by */ &&
need_distinct(rel) &&
diff --git
a/sql/test/BugTracker-2026/Tests/7966-optimize-select-distinct-from-union-queries.test
b/sql/test/BugTracker-2026/Tests/7966-optimize-select-distinct-from-union-queries.test
---
a/sql/test/BugTracker-2026/Tests/7966-optimize-select-distinct-from-union-queries.test
+++
b/sql/test/BugTracker-2026/Tests/7966-optimize-select-distinct-from-union-queries.test
@@ -50,7 +50,7 @@ FROM (
WHERE a >= 200
) AS u
----
-group by (
+project (
| group by (
| | munion (
| | | project (
@@ -65,7 +65,7 @@ group by (
| | | ) [ "outer_distinct_union_t"."a" ]
| | ) [ "outer_distinct_union_t"."a" as "u"."a" ]
| ) [ "u"."a" ] [ "u"."a" ]
-) [ "u"."a" ] [ "u"."a" ]
+) [ "u"."a" ]
statement ok
DROP TABLE IF EXISTS outer_distinct_union_t
diff --git
a/sql/test/BugTracker-2026/Tests/7968-optimize-distinct-from-q1-except-q2.test
b/sql/test/BugTracker-2026/Tests/7968-optimize-distinct-from-q1-except-q2.test
---
a/sql/test/BugTracker-2026/Tests/7968-optimize-distinct-from-q1-except-q2.test
+++
b/sql/test/BugTracker-2026/Tests/7968-optimize-distinct-from-q1-except-q2.test
@@ -51,7 +51,7 @@ SELECT COUNT(*) as c FROM rhs
query T nosort
EXPLAIN SELECT DISTINCT s.v FROM ((SELECT v FROM lhs) EXCEPT (SELECT v FROM
rhs)) s
----
-group by (
+project (
| distinct except (
| | project (
| | | table("sys"."lhs") [ "lhs"."v" ]
@@ -60,7 +60,7 @@ group by (
| | | table("sys"."rhs") [ "rhs"."v" ]
| | ) [ "rhs"."v" ]
| ) [ "lhs"."v" as "s"."v" ]
-) [ "s"."v" ] [ "s"."v" ]
+) [ "s"."v" ]
query T nosort
EXPLAIN SELECT s.v FROM ((SELECT v FROM lhs) EXCEPT (SELECT v FROM
rhs)) s
diff --git
a/sql/test/BugTracker-2026/Tests/7969-optimize-distinct-from-q1-intersect-q2.test
b/sql/test/BugTracker-2026/Tests/7969-optimize-distinct-from-q1-intersect-q2.test
---
a/sql/test/BugTracker-2026/Tests/7969-optimize-distinct-from-q1-intersect-q2.test
+++
b/sql/test/BugTracker-2026/Tests/7969-optimize-distinct-from-q1-intersect-q2.test
@@ -51,7 +51,7 @@ SELECT COUNT(*) as c FROM rhs
query T nosort
EXPLAIN SELECT DISTINCT s.v FROM ((SELECT v FROM lhs) INTERSECT (SELECT v FROM
rhs)) s
----
-group by (
+project (
| distinct intersect (
| | project (
| | | table("sys"."lhs") [ "lhs"."v" ]
@@ -60,7 +60,7 @@ group by (
| | | table("sys"."rhs") [ "rhs"."v" ]
| | ) [ "rhs"."v" ]
| ) [ "lhs"."v" as "s"."v" ]
-) [ "s"."v" ] [ "s"."v" ]
+) [ "s"."v" ]
query T nosort
EXPLAIN SELECT s.v FROM ((SELECT v FROM lhs) INTERSECT (SELECT v FROM
rhs)) s
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
