Changeset: 76649af0f3e0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/76649af0f3e0
Branch: default
Log Message:
Merges branch const_aggr_elim
diffs (truncated from 377 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
@@ -2504,9 +2504,91 @@ rel_distinct_aggregate_on_unique_values(
static inline sql_rel *
rel_remove_const_aggr(visitor *v, sql_rel *rel)
{
- if (!rel)
+ if(!rel) {
+ return rel;
+ }
+
+ list *exps = rel->exps;
+
+ if(rel->op != op_groupby || list_empty(exps)) {
return rel;
- if (rel && is_groupby(rel->op) && list_length(rel->exps) >= 1 &&
!rel_is_ref(rel)) {
+ }
+
+ if(!list_empty(rel->r)) {
+ /* in the general case in an expression of an aggregate over
+ * a constant can be rewritten as just the const e.g.
+ * aggr(const) -> const
+ */
+
+ for(node *n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+
+ if(e->type != e_aggr) {
+ continue;
+ }
+
+ sql_func *j = ((sql_subfunc *)e->f)->func;
+
+ /* some aggregates with const values can only be
eliminated
+ * under certain circumstances e.g.
+ * sum(NULL) -> NULL, sum(0) -> 0
+ * prod(NULL) -> NULL, prod(1) -> 1
+ * count(NULL) -> 0
+ */
+ int sum = strcmp(j->base.name, "sum") == 0,
+ prd = strcmp(j->base.name, "prod") == 0,
+ cnt = strcmp(j->base.name, "count") == 0;
+
+ if(!j->s && j->system == 1) {
+ list *se = e->l;
+
+ if(se == NULL) {
+ continue;
+ }
+
+ for(node *m = se->h; m; m = m->next) {
+ sql_exp *w = m->data;
+
+ if(w->type == e_atom && w->card ==
CARD_ATOM) {
+ atom *wa = w->l;
+
+ if(sum && !(wa->isnull ||
atom_is_zero(wa))) {
+ continue;
+ }
+
+ if(prd && !(wa->isnull ||
atom_is_one(wa))) {
+ continue;
+ }
+
+ if(cnt) {
+ if(wa->isnull) {
+
list_remove_node(se, NULL, m);
+
+
w=exp_atom_lng(v->sql->sa, 0);
+ list_append(se,
w);
+ }
+ else {
+ continue;
+ }
+ }
+
+
exp_setalias(w,e->alias.label,e->alias.rname,e->alias.name);
+
+ n->data = w;
+ v->changes++;
+ }
+ }
+ }
+ }
+ }
+
+ /*
+ * Below code replaces GROUP BY with PROJECT in some cases;
+ * Triggers on...
+ * select 1 having true; select 42 from foo group by x; select n from
foo group by rollup(n);
+ */
+
+ if (!rel_is_ref(rel)) {
int needed = 0;
for (node *n = rel->exps->h; n; n = n->next) {
sql_exp *exp = (sql_exp*) n->data;
@@ -2524,6 +2606,7 @@ rel_remove_const_aggr(visitor *v, sql_re
if (exp_is_atom(exp))
atoms++;
}
+ /* possible edge case, never triggers in
coverage tests */
if (atoms == list_length(rel->r)) {
list *nexps = sa_list(v->sql->sa);
for (node *n = rel->exps->h; n; ) {
@@ -2588,6 +2671,7 @@ rel_remove_const_aggr(visitor *v, sql_re
return nrel;
}
}
+
return rel;
}
@@ -3035,6 +3119,7 @@ rel_optimize_projections_(visitor *v, sq
return rel;
rel = rel_remove_const_aggr(v, rel);
+
if (v->value_based_opt) {
rel = rel_simplify_sum(v, rel);
rel = rel_simplify_groupby_columns(v, rel);
@@ -3046,6 +3131,7 @@ rel_optimize_projections_(visitor *v, sq
rel = rel_distinct_aggregate_on_unique_values(v, rel);
rel = rel_groupby_distinct(v, rel);
rel = rel_push_count_down(v, rel);
+
/* only when value_based_opt is on, ie not for dependency resolution */
if (v->value_based_opt) {
rel = rel_simplify_count(v, rel);
diff --git a/sql/server/sql_atom.c b/sql/server/sql_atom.c
--- a/sql/server/sql_atom.c
+++ b/sql/server/sql_atom.c
@@ -1080,6 +1080,33 @@ atom_is_zero(atom *a)
}
int
+atom_is_one(atom *a)
+{
+ if (a->isnull || !ATOMlinear(a->tpe.type->localtype))
+ return 0;
+ switch (ATOMstorage(a->tpe.type->localtype)) {
+ case TYPE_bte:
+ return a->data.val.btval == 1;
+ case TYPE_sht:
+ return a->data.val.shval == 1;
+ case TYPE_int:
+ return a->data.val.ival == 1;
+ case TYPE_lng:
+ return a->data.val.lval == 1;
+#ifdef HAVE_HGE
+ case TYPE_hge:
+ return a->data.val.hval == 1;
+#endif
+ case TYPE_flt:
+ return a->data.val.fval == 1;
+ case TYPE_dbl:
+ return a->data.val.dval == 1;
+ default:
+ return 0;
+ }
+}
+
+int
atom_is_true(atom *a)
{
if (a->isnull)
diff --git a/sql/server/sql_atom.h b/sql/server/sql_atom.h
--- a/sql/server/sql_atom.h
+++ b/sql/server/sql_atom.h
@@ -65,6 +65,7 @@ extern atom *atom_inc(allocator *sa, ato
extern int atom_is_true(atom *a);
extern int atom_is_false(atom *a);
extern int atom_is_zero(atom *a);
+extern int atom_is_one(atom *a);
extern unsigned int atom_digits(atom *a);
diff --git a/sql/test/rel-optimizers/Tests/All
b/sql/test/rel-optimizers/Tests/All
--- a/sql/test/rel-optimizers/Tests/All
+++ b/sql/test/rel-optimizers/Tests/All
@@ -15,4 +15,5 @@ merge-unions
merge-ors-base
merge-ors-single-col-eq-to-cmp_in
merge-ors-multi-col-eq-to-cmp_in
+const-aggr-elim
isnull-isnotnull-equality-exp
diff --git a/sql/test/rel-optimizers/Tests/const-aggr-elim.test
b/sql/test/rel-optimizers/Tests/const-aggr-elim.test
new file mode 100644
--- /dev/null
+++ b/sql/test/rel-optimizers/Tests/const-aggr-elim.test
@@ -0,0 +1,186 @@
+statement ok
+CREATE TABLE baz (a INT, b INT)
+
+statement ok
+INSERT INTO baz (a, b) VALUES (42, 1), (31, 6), (32, 6), (36, 7), (57, 3)
+
+query I rowsort
+SELECT avg(b) FROM baz GROUP BY b
+----
+1
+3
+6
+7
+
+query I rowsort
+select avg(3) from generate_series(cast(0 as integer), 10, 1);
+----
+3
+
+query T nosort
+PLAN SELECT avg(3) FROM generate_series(cast(0 as integer), 10, 1);
+----
+project (
+| group by (
+| | table ("sys"."generate_series"(int(31) "0", int(4) "10", int(1) "1"),
+| | ) [ "%1"."value" ]
+| ) [ ] [ "sys"."avg" no nil (tinyint(2) "3") UNIQUE as "%2"."%2" ]
+) [ "%2"."%2" UNIQUE ]
+
+query I rowsort
+SELECT avg(3) FROM baz GROUP BY b
+----
+3
+3
+3
+3
+
+query T nosort
+PLAN SELECT avg(3) FROM baz GROUP BY b
+----
+project (
+| group by (
+| | table("sys"."baz") [ "baz"."b" NOT NULL ]
+| ) [ "baz"."b" NOT NULL ] [ "baz"."b" NOT NULL ]
+) [ tinyint(2) "3" as "%1"."%1" ]
+
+query I rowsort
+SELECT 1 + avg(3) FROM baz GROUP BY b
+----
+4
+4
+4
+4
+
+query T nosort
+PLAN SELECT 1 + avg(3) FROM baz GROUP BY b
+----
+project (
+| group by (
+| | table("sys"."baz") [ "baz"."b" NOT NULL ]
+| ) [ "baz"."b" NOT NULL ] [ "baz"."b" NOT NULL ]
+) [ "sys"."sql_add"(double(53) "1", tinyint(2) "3") NOT NULL ]
+
+query I rowsort
+SELECT avg(NULL) FROM baz GROUP BY b
+----
+NULL
+NULL
+NULL
+NULL
+
+query II rowsort
+SELECT 1 + avg(3), avg(3) * 10 FROM baz GROUP BY b
+----
+4
+30
+4
+30
+4
+30
+4
+30
+
+query T nosort
+PLAN SELECT 1 + avg(3), avg(3) * 10 FROM baz GROUP BY b
+----
+project (
+| project (
+| | group by (
+| | | table("sys"."baz") [ "baz"."b" NOT NULL ]
+| | ) [ "baz"."b" NOT NULL ] [ "baz"."b" NOT NULL ]
+| ) [ tinyint(2) "3" as "%1"."%1" ]
+) [ "sys"."sql_add"(double(53) "1", "%1"."%1" NOT NULL) NOT NULL,
"sys"."sql_mul"("%1"."%1" NOT NULL, double(53) "10") NOT NULL ]
+
+query II rowsort
+SELECT avg(3), avg(b) FROM baz GROUP BY b
+----
+3
+1
+3
+3
+3
+6
+3
+7
+
+query I rowsort
+SELECT count(3) FROM baz GROUP BY b
+----
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
