Changeset: 9ac54f723bf4 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9ac54f723bf4
Modified Files:
sql/server/rel_optimizer.c
sql/test/Tests/keys.test
sql/test/Tests/order_by_complex_exp.test
Branch: default
Log Message:
Backported count(not null) -> count(*) optimizer from properties branch into
default
diffs (208 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
@@ -9523,7 +9523,7 @@ rel_optimize_unions_topdown(visitor *v,
return rel;
}
-static sql_rel *
+static inline sql_rel *
rel_basecount(visitor *v, sql_rel *rel)
{
if (is_groupby(rel->op) && rel->l && !rel->r && list_length(rel->exps)
== 1 && exp_aggr_is_count(rel->exps->h->data)) {
@@ -9549,6 +9549,54 @@ rel_basecount(visitor *v, sql_rel *rel)
return rel;
}
+static inline sql_rel *
+rel_simplify_count(visitor *v, sql_rel *rel)
+{
+ if (is_groupby(rel->op) && !list_empty(rel->exps)) {
+ mvc *sql = v->sql;
+ int ncountstar = 0;
+
+ /* Convert count(no null) into count(*) */
+ for (node *n = rel->exps->h; n ; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (exp_aggr_is_count(e) && !need_distinct(e)) {
+ if (list_length(e->l) == 0) {
+ ncountstar++;
+ } else if (list_length(e->l) == 1 &&
exp_is_not_null((sql_exp*)((list*)e->l)->h->data)) {
+ sql_subfunc *cf = sql_bind_func(sql,
"sys", "count", sql_bind_localtype("void"), NULL, F_AGGR);
+ sql_exp *ne = exp_aggr(sql->sa, NULL,
cf, 0, 0, e->card, 0);
+ if (exp_name(e))
+ exp_prop_alias(sql->sa, ne, e);
+ n->data = ne;
+ ncountstar++;
+ v->changes++;
+ }
+ }
+ }
+ /* With multiple count(*), use exp_ref to reduce the number of
calls to this aggregate */
+ if (ncountstar > 1) {
+ sql_exp *count_star = NULL;
+ for (node *n = rel->exps->h; n ; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (exp_aggr_is_count(e) && !need_distinct(e)
&& list_length(e->l) == 0) {
+ if (!count_star) {
+ count_star = e;
+ } else {
+ sql_exp *ne = exp_ref(sql,
count_star);
+ if (exp_name(e))
+ exp_prop_alias(sql->sa,
ne, e);
+ n->data = ne;
+ v->changes++;
+ }
+ }
+ }
+ }
+ }
+ return rel;
+}
+
static sql_rel *
rel_optimize_projections(visitor *v, sql_rel *rel)
{
@@ -9563,8 +9611,11 @@ rel_optimize_projections(visitor *v, sql
rel = rel_reduce_groupby_exps(v, rel);
rel = rel_groupby_distinct(v, rel);
rel = rel_push_count_down(v, rel);
- if (v->value_based_opt) /* only when value_based_opt is on, ie not for
dependency resolution */
+ /* only when value_based_opt is on, ie not for dependency resolution */
+ if (v->value_based_opt) {
rel = rel_basecount(v, rel);
+ rel = rel_simplify_count(v, rel);
+ }
return rel;
}
diff --git a/sql/test/Tests/keys.test b/sql/test/Tests/keys.test
--- a/sql/test/Tests/keys.test
+++ b/sql/test/Tests/keys.test
@@ -194,20 +194,28 @@ query T nosort
plan select count(distinct a) from dummyme
----
project (
-| group by (
-| | table("sys"."dummyme") [ "dummyme"."a" NOT NULL HASHCOL ] COUNT
-| ) [ ] [ "sys"."count" no nil ("dummyme"."a" NOT NULL HASHCOL ) NOT NULL as
"%1"."%1" ]
+| [ "sys"."cnt"(clob "sys", clob "dummyme") NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct a) from dummyme
+----
+3
+
query T nosort
PLAN select count(distinct a) from dummyme group by b
----
project (
| group by (
-| | table("sys"."dummyme") [ "dummyme"."a" NOT NULL HASHCOL , "dummyme"."b" ]
COUNT
-| ) [ "dummyme"."b" ] [ "sys"."count" no nil ("dummyme"."a" NOT NULL HASHCOL )
NOT NULL as "%1"."%1" ]
+| | table("sys"."dummyme") [ "dummyme"."b" ] COUNT
+| ) [ "dummyme"."b" ] [ "sys"."count"() NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct a) from dummyme group by b
+----
+3
+
query T nosort
/* not eliminated */
plan select count(distinct b) from dummyme
@@ -218,6 +226,11 @@ project (
| ) [ ] [ "sys"."count" unique no nil ("dummyme"."b") NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct b) from dummyme
+----
+1
+
query T nosort
PLAN select count(distinct a + 1) from dummyme
----
@@ -229,6 +242,11 @@ project (
| ) [ ] [ "sys"."count" unique no nil ("%2"."%2" NOT NULL) NOT NULL as
"%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct a + 1) from dummyme
+----
+3
+
query T nosort
PLAN select count(distinct a + b) from dummyme
----
@@ -240,6 +258,11 @@ project (
| ) [ ] [ "sys"."count" unique no nil ("%2"."%2") NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct a + b) from dummyme
+----
+3
+
query T nosort
PLAN select count(distinct abs(a)) from dummyme
----
@@ -251,6 +274,42 @@ project (
| ) [ ] [ "sys"."count" unique no nil ("%2"."%2" NOT NULL) NOT NULL as
"%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
+query I nosort
+select count(distinct abs(a)) from dummyme
+----
+3
+
+query T nosort
+PLAN select count(abs(a)) from dummyme
+----
+project (
+| group by (
+| | project (
+| | | table("sys"."dummyme") [ "dummyme"."a" NOT NULL HASHCOL ] COUNT
+| | ) [ "sys"."abs"("dummyme"."a" NOT NULL HASHCOL ) NOT NULL as "%2"."%2" ]
+| ) [ ] [ "sys"."count"() NOT NULL as "%1"."%1" ]
+) [ "%1"."%1" NOT NULL ]
+
+query I nosort
+select count(abs(a)) from dummyme
+----
+3
+
+query T nosort
+PLAN select count(a) as x, count(*) as y from dummyme
+----
+project (
+| group by (
+| | table("sys"."dummyme") [ "dummyme"."%TID%" NOT NULL ] COUNT
+| ) [ ] [ "sys"."count"() NOT NULL as "%1"."%1", "%1"."%1" NOT NULL as
"%2"."%2" ]
+) [ "%1"."%1" NOT NULL as "x", "%2"."%2" NOT NULL as "y" ]
+
+query II nosort
+select count(a) as x, count(*) as y from dummyme
+----
+3
+3
+
statement ok
ROLLBACK
diff --git a/sql/test/Tests/order_by_complex_exp.test
b/sql/test/Tests/order_by_complex_exp.test
--- a/sql/test/Tests/order_by_complex_exp.test
+++ b/sql/test/Tests/order_by_complex_exp.test
@@ -44,7 +44,7 @@ order by count(col1), col1
project (
| group by (
| | table("sys"."test02") [ "test02"."col1" NOT NULL ] COUNT
-| ) [ "test02"."col1" NOT NULL ] [ "test02"."col1" NOT NULL, "sys"."count" no
nil ("test02"."col1" NOT NULL) NOT NULL as "%1"."%1" ]
+| ) [ "test02"."col1" NOT NULL ] [ "test02"."col1" NOT NULL, "sys"."count"()
NOT NULL as "%1"."%1" ]
) [ "test02"."col1" NOT NULL ] [ "%1"."%1" ASC NOT NULL, "test02"."col1" ASC
NOT NULL ]
query T rowsort
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
