Changeset: 9286d3336613 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9286d3336613
Added Files:
sql/test/BugTracker-2025/Tests/7752-union-sum.test
Modified Files:
sql/server/rel_optimize_proj.c
sql/server/rel_optimizer.c
sql/test/BugTracker-2025/Tests/All
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/rel-optimizers/Tests/merge-unions.test
Branch: Dec2025
Log Message:
do merge of unions earlier (give less nested unions/group bys)
Fixes bug #7752
diffs (164 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
@@ -60,6 +60,7 @@ rel_used_projections(mvc *sql, list *exp
static sql_rel *
rel_push_project_down_(visitor *v, sql_rel *rel)
{
+ sql_rel *l = rel->l;
/* for now only push down renames */
if (v->depth > 1 && is_simple_project(rel->op) && !need_distinct(rel)
&& !rel_is_ref(rel) && rel->l && !rel->r &&
v->parent &&
@@ -91,6 +92,24 @@ rel_push_project_down_(visitor *v, sql_r
}
}
}
+ if (v->depth > 1 && is_simple_project(rel->op) && !need_distinct(rel)
&& !rel_is_ref(rel) && rel->l && !rel->r &&
+ is_munion(l->op) && !rel_is_ref(l) &&
+ list_length(rel->exps) == list_length(l->exps) &&
+ list_check_prop_all(rel->exps,
(prop_check_func)&exp_is_useless_rename)) {
+ bool all = true;
+
+ for (node *n = rel->exps->h, *m = l->exps->h; n && m && all; n
= n->next, m = m->next) {
+ sql_exp *eo = n->data, *ei = m->data;
+ if (eo->nid != eo->alias.label || ei->alias.label !=
eo->nid)
+ all = false;
+ }
+ if (all) {
+ rel->l = NULL;
+ rel_destroy(v->sql, rel);
+ v->changes++;
+ return l;
+ }
+ }
/* ToDo handle useful renames, ie new relation name and unique set of
attribute names (could reduce set of * attributes) */
/* handle both useless and useful with project [ group by ] */
return rel;
@@ -3366,7 +3385,7 @@ rel_merge_unions(visitor *v, sql_rel *re
for(node *n = l->h; n; ) {
node *next = n->next;
sql_rel *c = n->data;
- if (is_munion(c->op)) {
+ if (is_munion(c->op) && (need_distinct(rel) ||
!need_distinct(c))) {
c = rel_dup(c);
list_remove_node(l, NULL, n);
l = list_join(l, c->l);
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
@@ -566,12 +566,12 @@ const sql_optimizer pre_sql_optimizers[]
{ 8, "optimize_select_and_joins_bottomup",
bind_optimize_select_and_joins_bottomup},
{ 9, "project_reduce_casts", bind_project_reduce_casts},
{10, "optimize_unions_bottomup", bind_optimize_unions_bottomup},
- {11, "optimize_projections", bind_optimize_projections},
- {12, "optimize_joins", bind_optimize_joins},
- {13, "join_order", bind_join_order},
- {14, "optimize_semi_and_anti", bind_optimize_semi_and_anti},
- {15, "optimize_select_and_joins_topdown",
bind_optimize_select_and_joins_topdown},
- {16, "optimize_unions_topdown", bind_optimize_unions_topdown},
+ {11, "optimize_unions_topdown", bind_optimize_unions_topdown},
+ {12, "optimize_projections", bind_optimize_projections},
+ {13, "optimize_joins", bind_optimize_joins},
+ {14, "join_order", bind_join_order},
+ {15, "optimize_semi_and_anti", bind_optimize_semi_and_anti},
+ {16, "optimize_select_and_joins_topdown",
bind_optimize_select_and_joins_topdown},
{17, "dce", bind_dce},
{18, "push_func_and_select_down", bind_push_func_and_select_down},
{19, "push_topn_and_sample_down", bind_push_topn_and_sample_down},
diff --git a/sql/test/BugTracker-2025/Tests/7752-union-sum.test
b/sql/test/BugTracker-2025/Tests/7752-union-sum.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7752-union-sum.test
@@ -0,0 +1,4 @@
+query I
+select sum(1) from ( select null abc from schemas union all select null abc
union all select null abc )
+----
+8
diff --git a/sql/test/BugTracker-2025/Tests/All
b/sql/test/BugTracker-2025/Tests/All
--- a/sql/test/BugTracker-2025/Tests/All
+++ b/sql/test/BugTracker-2025/Tests/All
@@ -78,5 +78,6 @@ 7748-update-returning-subquery-crash
7753-like-in-projection
7725-crash-find-rel-ref
7745-antijoin-bad-mal-gen
+7752-union-sum
7756-anti-join-null
7759-replace-wrong-error
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -886,7 +886,7 @@ DEP_FUNC
query TTT nosort
SELECT distinct c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables
as v, sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-1788 values hashing to a7640c35ae84fb0995f708b8b88e01d0
+1773 values hashing to f976ca5f8d89a24a8e824db233ce9c07
query TTT nosort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -3595,11 +3595,8 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "keys", "name", "information_schema",
"check_constraints", "VIEW" ]
[ "column used by view", "sys", "keys", "table_id",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "sys", "keys", "type", "information_schema",
"check_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "authorization",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "id",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "name",
"information_schema", "check_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "owner",
"information_schema", "check_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "system",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "id",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "name",
"information_schema", "check_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "schema_id",
"information_schema", "check_constraints", "VIEW" ]
@@ -3713,11 +3710,8 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "keys", "name", "information_schema",
"table_constraints", "VIEW" ]
[ "column used by view", "sys", "keys", "table_id",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "sys", "keys", "type", "information_schema",
"table_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "authorization",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "id",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "name",
"information_schema", "table_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "owner",
"information_schema", "table_constraints", "VIEW" ]
-[ "column used by view", "sys", "schemas", "system",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "id",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "name",
"information_schema", "table_constraints", "VIEW" ]
[ "column used by view", "tmp", "_tables", "schema_id",
"information_schema", "table_constraints", "VIEW" ]
diff --git a/sql/test/rel-optimizers/Tests/merge-unions.test
b/sql/test/rel-optimizers/Tests/merge-unions.test
--- a/sql/test/rel-optimizers/Tests/merge-unions.test
+++ b/sql/test/rel-optimizers/Tests/merge-unions.test
@@ -248,25 +248,17 @@ project (
| group by (
| | munion (
| | | group by (
-| | | | munion (
-| | | | | group by (
-| | | | | | table("sys"."f1") [ "f1"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
-| | | | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ],
-| | | | | group by (
-| | | | | | table("sys"."f2") [ "f2"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
-| | | | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ]
-| | | | ) [ "f_merge_rec"."n" NOT NULL, "%1"."%1" NOT NULL ]
-| | | ) [ "f_merge_rec"."n" NOT NULL ] [ "f_merge_rec"."n" NOT NULL,
"sys"."sum" no nil ("%1"."%1" NOT NULL) NOT NULL as "%1"."%1" ],
+| | | | table("sys"."f1") [ "f1"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
+| | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ],
+| | | group by (
+| | | | table("sys"."f2") [ "f2"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
+| | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ],
| | | group by (
-| | | | munion (
-| | | | | group by (
-| | | | | | table("sys"."f3") [ "f3"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
-| | | | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ],
-| | | | | group by (
-| | | | | | table("sys"."f4") [ "f4"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
-| | | | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ]
-| | | | ) [ "f_merge_rec"."n" NOT NULL, "%1"."%1" NOT NULL ]
-| | | ) [ "f_merge_rec"."n" NOT NULL ] [ "f_merge_rec"."n" NOT NULL,
"sys"."sum" no nil ("%1"."%1" NOT NULL) NOT NULL as "%1"."%1" ]
+| | | | table("sys"."f3") [ "f3"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
+| | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ],
+| | | group by (
+| | | | table("sys"."f4") [ "f4"."n" NOT NULL UNIQUE as "f_merge_rec"."n" ]
+| | | ) [ "f_merge_rec"."n" NOT NULL UNIQUE ] [ "f_merge_rec"."n" NOT NULL
UNIQUE, "sys"."count" no nil ("f_merge_rec"."n" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ]
| | ) [ "f_merge_rec"."n" NOT NULL, "%1"."%1" NOT NULL ]
| ) [ "f_merge_rec"."n" NOT NULL ] [ "f_merge_rec"."n" NOT NULL, "sys"."sum"
no nil ("%1"."%1" NOT NULL) NOT NULL as "%1"."%1" ]
) [ "f_merge_rec"."n" NOT NULL UNIQUE, "%1"."%1" NOT NULL ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
