Changeset: 3f6c10b09d8a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3f6c10b09d8a
Modified Files:
sql/server/rel_optimizer.c
sql/test/BugTracker-2016/Tests/CREATE_INDEX_breaks_table_queries.Bug-4053.stable.out
sql/test/Tests/keys.sql
sql/test/Tests/keys.stable.out
Branch: Nov2019
Log Message:
Added rel_distinct_aggregate_on_unique_values optimizer where transforms
aggr(distinct unique_val) -> aggr(unique_val)
diffs (218 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
@@ -2476,6 +2476,51 @@ rel_is_join_on_pkey( sql_rel *rel )
return 0;
}
+/* if all arguments to a distinct aggregate are unique, remove 'distinct'
property */
+static sql_rel *
+rel_distinct_aggregate_on_unique_values(int *changes, mvc *sql, sql_rel *rel)
+{
+ sql_rel *l = (sql_rel*) rel->l;
+
+ (void) sql;
+ if (rel->op == op_groupby && (!l || is_base(l->op))) {
+ for (node *n = rel->exps->h; n; n = n->next) {
+ sql_exp *exp = (sql_exp*) n->data;
+
+ if (exp->type == e_aggr && need_distinct(exp)) {
+ bool all_unique = true;
+
+ for (node *m = ((list*)exp->l)->h; m &&
all_unique; m = m->next) {
+ sql_exp *arg = (sql_exp*) m->data;
+
+ if (arg->card == CARD_ATOM) /*
constants are always unique */
+ continue;
+ else if (arg->type == e_column) {
+ fcmp cmp = (fcmp)&kc_column_cmp;
+ sql_column *c =
exp_find_column(rel, arg, -2);
+
+ if (c) {
+ /* column is the only
primary key column of its table */
+ if (find_prop(arg->p,
PROP_HASHCOL) && c->t->pkey && list_find(c->t->pkey->k.columns, c, cmp) != NULL
&& list_length(c->t->pkey->k.columns) == 1)
+ continue;
+ else if (c->unique ==
1) /* column has unique constraint */
+ continue;
+ else
+ all_unique =
false;
+ } else
+ all_unique = false;
+ } else
+ all_unique = false;
+ }
+ if (all_unique) {
+ set_nodistinct(exp);
+ *changes = 1;
+ }
+ }
+ }
+ }
+ return rel;
+}
static sql_rel *
rel_distinct_project2groupby(int *changes, mvc *sql, sql_rel *rel)
@@ -9005,6 +9050,7 @@ optimize_rel(mvc *sql, sql_rel *rel, int
rel = rel_case_fixup(&changes, sql, rel, 1);
if (value_based_opt)
rel = rewrite(sql, rel, &rel_simplify_math,
&changes);
+ rel = rewrite(sql, rel,
&rel_distinct_aggregate_on_unique_values, &changes);
rel = rewrite(sql, rel, &rel_distinct_project2groupby,
&changes);
}
}
diff --git
a/sql/test/BugTracker-2016/Tests/CREATE_INDEX_breaks_table_queries.Bug-4053.stable.out
b/sql/test/BugTracker-2016/Tests/CREATE_INDEX_breaks_table_queries.Bug-4053.stable.out
---
a/sql/test/BugTracker-2016/Tests/CREATE_INDEX_breaks_table_queries.Bug-4053.stable.out
+++
b/sql/test/BugTracker-2016/Tests/CREATE_INDEX_breaks_table_queries.Bug-4053.stable.out
@@ -84,20 +84,20 @@ stdout of test 'CREATE_INDEX_breaks_tabl
% int, bigint, int, int, timestamp, smallint, smallint,
clob, clob, clob, clob, int, clob, clob, clob, clob, clob,
clob, clob, clob, clob, clob, clob, clob, clob, clob, clob,
clob, clob, clob, clob, clob, clob, double, double, double, double,
double, double, double, double, double, double, double, double, double, int,
boolean, bigint, int, int, int # type
% 1, 1, 1, 1, 26, 1, 1, 0, 0, 0,
0, 1, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 24, 24, 24, 24, 24, 24, 24,
24, 24, 24, 24, 24, 24, 1, 5, 1, 1,
1, 1 # length
#SELECT count(distinct "anno_mese") FROM "sys"."farmap_movimento";
-% sys.L4 # table_name
-% L3 # name
+% sys.L2 # table_name
+% L2 # name
% bigint # type
% 1 # length
[ 0 ]
#SELECT count(distinct "articolo_id") FROM "sys"."farmap_movimento";
-% sys.L4 # table_name
-% L3 # name
+% .L2 # table_name
+% L2 # name
% bigint # type
% 1 # length
[ 0 ]
#SELECT count(distinct "farmacia_id") FROM "sys"."farmap_movimento";
-% sys.L4 # table_name
-% L3 # name
+% .L2 # table_name
+% L2 # name
% bigint # type
% 1 # length
[ 0 ]
diff --git a/sql/test/Tests/keys.sql b/sql/test/Tests/keys.sql
--- a/sql/test/Tests/keys.sql
+++ b/sql/test/Tests/keys.sql
@@ -1,3 +1,4 @@
+START TRANSACTION;
CREATE TABLE allnewtriples (id integer NOT NULL, subject integer NOT
NULL, predicate integer NOT NULL, "object" integer NOT NULL, explicit
boolean NOT NULL, CONSTRAINT unique_key UNIQUE(subject, predicate, "object"));
@@ -9,3 +10,18 @@ SELECT idxs.name, idxs."type", keys.name
FROM idxs LEFT JOIN keys on idxs.name = keys.name;
SELECT idxs.name, idxs."type", keys.name, keys."type"
FROM idxs JOIN keys on idxs.name = keys.name;
+
+/* test elimination of distinct restriction on aggregates */
+create table dummyme (a int primary key, b int);
+insert into dummyme values (1,1), (2,1), (3,1);
+
+/* eliminated */
+plan select count(distinct a) from dummyme;
+plan select count(distinct a) from dummyme group by b;
+
+/* not eliminated */
+plan select count(distinct b) from dummyme;
+plan select count(distinct a + 1) from dummyme;
+plan select count(distinct a + b) from dummyme;
+plan select count(distinct abs(a)) from dummyme;
+ROLLBACK;
diff --git a/sql/test/Tests/keys.stable.out b/sql/test/Tests/keys.stable.out
--- a/sql/test/Tests/keys.stable.out
+++ b/sql/test/Tests/keys.stable.out
@@ -119,6 +119,94 @@ stdout of test 'keys` in directory 'sql/
[ "pg_pkey_id_file_id", 0, "pg_pkey_id_file_id", 0 ]
[ "pg_fkey_file_id", 1, "pg_fkey_file_id", 2 ]
[ "unique_key", 0, "unique_key", 1 ]
+#create table dummyme (a int primary key, b int);
+#insert into dummyme values (1,1), (2,1), (3,1);
+[ 3 ]
+#plan select count(distinct a) from dummyme;
+% .plan # table_name
+% rel # name
+% clob # type
+% 85 # length
+project (
+| group by (
+| | table(sys.dummyme) [ "dummyme"."a" NOT NULL HASHCOL ] COUNT
+| ) [ ] [ sys.count no nil ("dummyme"."a" NOT NULL HASHCOL ) NOT NULL as
"L2"."L2" ]
+) [ "L2"."L2" NOT NULL ]
+#plan select count(distinct a) from dummyme group by b;
+% .plan # table_name
+% rel # name
+% clob # type
+% 113 # length
+project (
+| group by (
+| | table(sys.dummyme) [ "dummyme"."a" NOT NULL HASHCOL , "dummyme"."b" ]
COUNT
+| ) [ "dummyme"."b" ] [ "dummyme"."b", sys.count no nil ("dummyme"."a" NOT
NULL HASHCOL ) NOT NULL as "L1"."L1" ]
+) [ "L1"."L1" NOT NULL ]
+#plan select count(distinct b) from dummyme;
+% .plan # table_name
+% rel # name
+% clob # type
+% 54 # length
+project (
+| group by (
+| | group by (
+| | | table(sys.dummyme) [ "dummyme"."b" ] COUNT
+| | ) [ "dummyme"."b" as "L3"."L3" ] [ "L3"."L3" ]
+| ) [ ] [ sys.count no nil ("L3"."L3") as "L2"."L2" ]
+) [ "L2"."L2" ]
+#plan select count(distinct a + 1) from dummyme;
+% .plan # table_name
+% rel # name
+% clob # type
+% 148 # length
+project (
+| group by (
+| | group by (
+| | | project (
+| | | | project (
+| | | | | table(sys.dummyme) [ "dummyme"."a" NOT NULL HASHCOL ] COUNT
+| | | | ) [ "dummyme"."a" NOT NULL HASHCOL , bigint["dummyme"."a" NOT NULL
HASHCOL ] as "L4"."L4", sys.sql_add("L4"."L4", bigint "1") as "L5"."L5" ]
+| | | ) [ "L5"."L5" ]
+| | ) [ "L5"."L5" as "L7"."L7" ] [ "L7"."L7" ]
+| ) [ ] [ sys.count no nil ("L7"."L7") as "L2"."L2" ]
+) [ "L2"."L2" ]
+#plan select count(distinct a + b) from dummyme;
+% .plan # table_name
+% rel # name
+% clob # type
+% 198 # length
+project (
+| group by (
+| | group by (
+| | | project (
+| | | | project (
+| | | | | table(sys.dummyme) [ "dummyme"."a" NOT NULL HASHCOL , "dummyme"."b"
] COUNT
+| | | | ) [ "dummyme"."a" NOT NULL HASHCOL , "dummyme"."b",
bigint["dummyme"."a" NOT NULL HASHCOL ] as "L4"."L4", bigint["dummyme"."b"] as
"L5"."L5", sys.sql_add("L4"."L4", "L5"."L5") as "L6"."L6" ]
+| | | ) [ "L6"."L6" ]
+| | ) [ "L6"."L6" as "L10"."L10" ] [ "L10"."L10" ]
+| ) [ ] [ sys.count no nil ("L10"."L10") as "L2"."L2" ]
+) [ "L2"."L2" ]
+#plan select count(distinct abs(a)) from dummyme;
+% .plan # table_name
+% rel # name
+% clob # type
+% 100 # length
+project (
+| group by (
+| | group by (
+| | | project (
+| | | | project (
+| | | | | table(sys.dummyme) [ "dummyme"."a" NOT NULL HASHCOL ] COUNT
+| | | | ) [ "dummyme"."a" NOT NULL HASHCOL , sys.abs("dummyme"."a" NOT NULL
HASHCOL ) as "L4"."L4" ]
+| | | ) [ "L4"."L4" ]
+| | ) [ "L4"."L4" as "L6"."L6" ] [ "L6"."L6" ]
+| ) [ ] [ sys.count no nil ("L6"."L6") as "L2"."L2" ]
+) [ "L2"."L2" ]
+#ROLLBACK;
+
+# 15:20:15 >
+# 15:20:15 > "Done."
+# 15:20:15 >
# 16:23:54 >
# 16:23:54 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
