Changeset: c72cb267ac31 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c72cb267ac31
Modified Files:
sql/server/rel_exp.c
sql/server/rel_optimizer.c
sql/test/miscellaneous/Tests/simple_plans.sql
sql/test/miscellaneous/Tests/simple_plans.test
Branch: default
Log Message:
Added (a = b) or (a is null and b is null) -> a *= b optimization. This helps
on bug #6968
diffs (235 lines):
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -1729,25 +1729,22 @@ exp_is_true(sql_exp *e)
}
static inline bool
-exp_is_cmp_exp_is_false(sql_exp* e) {
- assert(e->type == e_cmp);
- assert(e->semantics && (e->flag == cmp_equal || e->flag == cmp_notequal));
- assert(e->f == NULL);
- sql_exp* l = e->l;
- sql_exp* r = e->r;
- assert (l && r);
-
- /* Handle 'v is x' and 'v is not x' expressions.
- * Other cases in is-semantics are unspecified.
- */
- if (e->flag == cmp_equal && !e->anti) {
- return ((exp_is_null(l) && exp_is_not_null(r)) || (exp_is_not_null(l)
&& exp_is_null(r)));
- }
- if (((e->flag == cmp_notequal) && !e->anti) || ((e->flag == cmp_equal) &&
e->anti) ) {
- return ((exp_is_null(l) && exp_is_null(r)) || (exp_is_not_null(l) &&
exp_is_not_null(r)));
- }
-
- return false;
+exp_is_cmp_exp_is_false(sql_exp* e)
+{
+ sql_exp *l = e->l;
+ sql_exp *r = e->r;
+ assert(e->type == e_cmp && e->f == NULL && l && r);
+
+ /* Handle 'v is x' and 'v is not x' expressions.
+ * Other cases in is-semantics are unspecified.
+ */
+ if (e->flag != cmp_equal && e->flag != cmp_notequal)
+ return false;
+ if (e->flag == cmp_equal && !e->anti)
+ return ((exp_is_null(l) && exp_is_not_null(r)) ||
(exp_is_not_null(l) && exp_is_null(r)));
+ if (((e->flag == cmp_notequal) && !e->anti) || ((e->flag == cmp_equal)
&& e->anti) )
+ return ((exp_is_null(l) && exp_is_null(r)) ||
(exp_is_not_null(l) && exp_is_not_null(r)));
+ return false;
}
static inline bool
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
@@ -7739,6 +7739,59 @@ rel_simplify_ifthenelse(visitor *v, sql_
return e;
}
+/* optimize (a = b) or (a is null and b is null) -> a = b with null semantics
*/
+static sql_exp *
+try_rewrite_equal_or_is_null(mvc *sql, sql_exp *or, list *l1, list *l2)
+{
+ if (list_length(l1) == 1) {
+ bool valid = true, first_is_null_found = false,
second_is_null_found = false;
+ sql_exp *cmp = l1->h->data;
+ sql_exp *first = cmp->l, *second = cmp->r;
+
+ if (is_compare(cmp->type) && !is_anti(cmp) && !cmp->f &&
cmp->flag == cmp_equal) {
+ for(node *n = l2->h ; n && valid; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (is_compare(e->type) && e->flag == cmp_equal
&& !e->f &&
+ !is_anti(e) && is_semantics(e) &&
exp_is_null(e->r)) {
+ if (exp_match_exp(first, e->l))
+ first_is_null_found = true;
+ else if (exp_match_exp(second, e->l))
+ second_is_null_found = true;
+ else
+ valid = false;
+ } else {
+ valid = false;
+ }
+ }
+ if (valid && first_is_null_found &&
second_is_null_found) {
+ sql_exp *res = exp_compare(sql->sa, first,
second, cmp->flag);
+ set_semantics(res);
+ if (exp_name(or))
+ exp_prop_alias(sql->sa, res, or);
+ return res;
+ }
+ }
+ }
+ return or;
+}
+
+static sql_exp *
+rel_merge_cmp_or_null(visitor *v, sql_rel *rel, sql_exp *e, int depth)
+{
+ (void) rel;
+ (void) depth;
+ if (is_compare(e->type) && e->flag == cmp_or && !is_anti(e)) {
+ sql_exp *ne = try_rewrite_equal_or_is_null(v->sql, e, e->l,
e->r);
+ if (ne != e)
+ return ne;
+ ne = try_rewrite_equal_or_is_null(v->sql, e, e->r, e->l);
+ if (ne != e)
+ return ne;
+ }
+ return e;
+}
+
static void split_exps(mvc *sql, list *exps, sql_rel *rel);
static int
@@ -9558,6 +9611,8 @@ optimize_rel(mvc *sql, sql_rel *rel, int
gp.cnt[op_left] || gp.cnt[op_right] || gp.cnt[op_full] ||
gp.cnt[op_semi] || gp.cnt[op_anti] ||
gp.cnt[op_select]) {
+
+ rel = rel_exp_visitor_bottomup(&v, rel, &rel_merge_cmp_or_null,
false);
rel = rel_visitor_bottomup(&v, rel, &rel_find_range);
if (value_based_opt) {
rel = rel_project_reduce_casts(&v, rel);
@@ -9595,14 +9650,13 @@ optimize_rel(mvc *sql, sql_rel *rel, int
if (gp.cnt[op_join])
rel = rel_visitor_topdown(&v, rel, &rel_push_select_down_join);
- if (gp.cnt[op_select])
- rel = rel_visitor_topdown(&v, rel, &rel_push_select_down_union);
-
- if (gp.cnt[op_union] && gp.cnt[op_select])
- rel = rel_visitor_bottomup(&v, rel,
&rel_remove_union_partitions);
-
- if (gp.cnt[op_select])
+ if (gp.cnt[op_select]) {
+ if (gp.cnt[op_union]) {
+ rel = rel_visitor_topdown(&v, rel,
&rel_push_select_down_union);
+ rel = rel_visitor_bottomup(&v, rel,
&rel_remove_union_partitions);
+ }
rel = rel_visitor_bottomup(&ev, rel, &rel_remove_empty_select);
+ }
if (gp.cnt[op_groupby]) {
rel = rel_visitor_topdown(&v, rel, &rel_push_aggr_down);
diff --git a/sql/test/miscellaneous/Tests/simple_plans.sql
b/sql/test/miscellaneous/Tests/simple_plans.sql
--- a/sql/test/miscellaneous/Tests/simple_plans.sql
+++ b/sql/test/miscellaneous/Tests/simple_plans.sql
@@ -75,6 +75,18 @@ create view view2 as SELECT * FROM tabel
create view view3 as SELECT * FROM tabel3 as a;
PLAN SELECT 1 FROM view1 s INNER JOIN view2 h ON s.id_nr = h.id_nr LEFT JOIN
view2 h2 ON h.id_nr = h2.id_nr INNER JOIN view3 a ON a.id_nr = s.id_nr;
+-- optimize (a = b) or (a is null and b is null) -> a = b with null semantics
+CREATE TABLE integers(i INTEGER, j INTEGER);
+INSERT INTO integers VALUES (1,4), (2,5), (3,6), (NULL,NULL);
+
+plan select i1.i, i2.i from integers i1 inner join integers i2 on i1.i = i2.i
or (i1.i is null and i2.i is null);
+select i1.i, i2.i from integers i1 inner join integers i2 on i1.i = i2.i or
(i1.i is null and i2.i is null);
+
+plan select i1.i, i2.i from integers i1 full outer join integers i2 on (i1.i
is null and i2.i is null) or i1.i = i2.i;
+select i1.i, i2.i from integers i1 full outer join integers i2 on (i1.i is
null and i2.i is null) or i1.i = i2.i;
+
+plan select i, j from integers where i = j or (j is null and i is null);
+select i, j from integers where i = j or (j is null and i is null);
rollback;
set optimizer='default_pipe';
diff --git a/sql/test/miscellaneous/Tests/simple_plans.test
b/sql/test/miscellaneous/Tests/simple_plans.test
--- a/sql/test/miscellaneous/Tests/simple_plans.test
+++ b/sql/test/miscellaneous/Tests/simple_plans.test
@@ -382,6 +382,71 @@ project (
) [ tinyint "1" ]
statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (1,4), (2,5), (3,6), (NULL,NULL)
+
+query T nosort
+plan select i1.i, i2.i from integers i1 inner join integers i2 on i1.i = i2.i
or (i1.i is null and i2.i is null)
+----
+project (
+| join (
+| | table("sys"."integers") [ "integers"."i" as "i1"."i" ] COUNT ,
+| | table("sys"."integers") [ "integers"."i" as "i2"."i" ] COUNT
+| ) [ "i1"."i" * = "i2"."i" ]
+) [ "i1"."i", "i2"."i" ]
+
+query II rowsort
+select i1.i, i2.i from integers i1 inner join integers i2 on i1.i = i2.i or
(i1.i is null and i2.i is null)
+----
+1
+1
+2
+2
+3
+3
+NULL
+NULL
+
+query T nosort
+plan select i1.i, i2.i from integers i1 full outer join integers i2 on (i1.i
is null and i2.i is null) or i1.i = i2.i
+----
+project (
+| full outer join (
+| | table("sys"."integers") [ "integers"."i" as "i1"."i" ] COUNT ,
+| | table("sys"."integers") [ "integers"."i" as "i2"."i" ] COUNT
+| ) [ "i1"."i" * = "i2"."i" ]
+) [ "i1"."i", "i2"."i" ]
+
+query II rowsort
+select i1.i, i2.i from integers i1 full outer join integers i2 on (i1.i is
null and i2.i is null) or i1.i = i2.i
+----
+1
+1
+2
+2
+3
+3
+NULL
+NULL
+
+query T nosort
+plan select i, j from integers where i = j or (i is null and j is null)
+----
+project (
+| select (
+| | table("sys"."integers") [ "integers"."i", "integers"."j" ] COUNT
+| ) [ "integers"."i" * = "integers"."j" ]
+) [ "integers"."i", "integers"."j" ]
+
+query II rowsort
+select i, j from integers where i = j or (i is null and j is null)
+----
+NULL
+NULL
+
+statement ok
rollback
statement ok
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
