Changeset: 04bd882874bd for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=04bd882874bd
Modified Files:
sql/server/rel_rewriter.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/rel_updates.c
sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
sql/test/subquery/Tests/subquery5.sql
Branch: Jun2020
Log Message:
Added isnull(not null) -> false optimization, plus fixed a bug in merge
statements, where a not nullable expression after being projected as a right
column on the left join was stil treated as not nullable
diffs (202 lines):
diff --git a/sql/server/rel_rewriter.c b/sql/server/rel_rewriter.c
--- a/sql/server/rel_rewriter.c
+++ b/sql/server/rel_rewriter.c
@@ -16,6 +16,7 @@
/* simplify expressions, such as not(not(x)) */
/* exp visitor */
+#define is_null(sf) (strcmp(sf->func->base.name, "isnull") == 0)
#define is_not_func(sf) (strcmp(sf->func->base.name, "not") == 0)
#define is_not_anyequal(sf) (strcmp(sf->func->base.name, "sql_not_anyequal")
== 0)
@@ -115,6 +116,7 @@ rewrite_simplify_exp(mvc *sql, sql_rel *
ie = args->h->data;
if (exp_name(e))
exp_prop_alias(sql->sa, ie, e);
+ (*changes)++;
return ie;
}
if (is_func(ie->type) && list_length(ie->l) == 2 &&
is_not_anyequal(sf)) {
@@ -126,6 +128,7 @@ rewrite_simplify_exp(mvc *sql, sql_rel *
ie = exp_in_func(sql, l, vals, 1, 0);
if (exp_name(e))
exp_prop_alias(sql->sa, ie, e);
+ (*changes)++;
return ie;
}
/* TRUE or X -> TRUE
@@ -138,24 +141,50 @@ rewrite_simplify_exp(mvc *sql, sql_rel *
if (list_length(l) == 1) {
sql_exp *ie = l->h->data;
- if (exp_is_true(sql, ie))
+ if (exp_is_true(sql, ie)) {
+ (*changes)++;
return ie;
- else if (exp_is_false(sql, ie) &&
list_length(r) == 1)
+ } else if (exp_is_false(sql, ie) &&
list_length(r) == 1) {
+ (*changes)++;
return r->h->data;
+ }
} else if (list_length(l) == 0) { /* left is true */
+ (*changes)++;
return exp_atom_bool(sql->sa, 1);
}
if (list_length(r) == 1) {
sql_exp *ie = r->h->data;
- if (exp_is_true(sql, ie))
+ if (exp_is_true(sql, ie)) {
+ (*changes)++;
return ie;
- else if (exp_is_false(sql, ie) &&
list_length(l) == 1)
+ } else if (exp_is_false(sql, ie) &&
list_length(l) == 1) {
+ (*changes)++;
return l->h->data;
+ }
} else if (list_length(r) == 0) { /* right is true */
+ (*changes)++;
return exp_atom_bool(sql->sa, 1);
}
}
+ } else if (is_func(e->type) && list_length(e->l) == 1 && is_null(sf)) {
+ list *args = e->l;
+ sql_exp *ie = args->h->data;
+
+ if (!has_nil(ie) || exp_is_false(sql, ie) || exp_is_true(sql,
ie) || exp_is_not_null(sql, ie)) { /* is null on something that is never null,
is always false */
+ ie = exp_atom_bool(sql->sa, 0);
+ if (exp_name(e))
+ exp_prop_alias(sql->sa, ie, e);
+ (*changes)++;
+ return ie;
+ }
+ if (exp_is_null(sql, ie)) { /* is null on something that is
always null, is always true */
+ ie = exp_atom_bool(sql->sa, 1);
+ if (exp_name(e))
+ exp_prop_alias(sql->sa, ie, e);
+ (*changes)++;
+ return ie;
+ }
}
return e;
}
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -2003,6 +2003,7 @@ exp_exist(sql_query *query, sql_rel *rel
mvc *sql = query->sql;
sql_subfunc *exists_func = NULL;
sql_subtype *t;
+ sql_exp *res;
if (!exp_name(le))
exp_label(sql->sa, le, ++sql->label);
@@ -2017,7 +2018,9 @@ exp_exist(sql_query *query, sql_rel *rel
if (!exists_func)
return sql_error(sql, 02, SQLSTATE(42000) "exist operator on
type %s missing", t->type->sqlname);
- return exp_unop(sql->sa, le, exists_func);
+ res = exp_unop(sql->sa, le, exists_func);
+ set_has_no_nil(res);
+ return res;
}
static sql_exp *
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -2448,7 +2448,9 @@ exp_exist(mvc *sql, sql_exp *le, sql_exp
le = rel_nop_(sql, NULL, ne, exp_atom_bool(sql->sa, !exists),
exp_atom_bool(sql->sa, exists), NULL, NULL, "ifthenelse", card_value);
return le;
} else {
- return exp_unop(sql->sa, le, exists_func);
+ sql_exp *res = exp_unop(sql->sa, le, exists_func);
+ set_has_no_nil(res);
+ return res;
}
}
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -1343,6 +1343,7 @@ merge_into_table(sql_query *query, dlist
symbol *sym = m->data.sym, *opt_search, *action;
tokens token = sym->token;
dlist* dl = sym->data.lval, *sts;
+ list *nexps;
opt_search = dl->h->data.sym;
action = dl->h->next->data.sym;
sts = action->data.lval;
@@ -1370,7 +1371,10 @@ merge_into_table(sql_query *query, dlist
}
//project columns of both bt and joined + oid
- extra_project = rel_project(sql->sa, join_rel,
rel_projections(sql, bt, NULL, 1, 0));
+ nexps = rel_projections(sql, bt, NULL, 1, 0);
+ for (node *n = nexps->h ; n ; n = n->next) /*
after going through the left outer join, a NOT NULL column may have NULL values
*/
+ set_has_nil((sql_exp*)n->data);
+ extra_project = rel_project(sql->sa, join_rel,
nexps);
extra_project->exps =
list_merge(extra_project->exps, rel_projections(sql, joined, NULL, 1, 0),
(fdup)NULL);
list_append(extra_project->exps,
exp_column(sql->sa, bt_name, TID, sql_bind_localtype("oid"), CARD_MULTI, 0, 1));
@@ -1383,7 +1387,10 @@ merge_into_table(sql_query *query, dlist
extra_select = rel_select(sql->sa,
extra_project, exp_compare(sql->sa, nils, exp_atom_bool(sql->sa, 0),
cmp_equal));
//the update statement requires a projection on
the right side
- extra_project = rel_project(sql->sa,
extra_select, rel_projections(sql, bt, NULL, 1, 0));
+ nexps = rel_projections(sql, bt, NULL, 1, 0);
+ for (node *n = nexps->h ; n ; n = n->next) /*
after going through the left outer join, a NOT NULL column may have NULL values
*/
+ set_has_nil((sql_exp*)n->data);
+ extra_project = rel_project(sql->sa,
extra_select, nexps);
extra_project->exps =
list_merge(extra_project->exps, rel_projections(sql, joined, NULL, 1, 0),
(fdup)NULL);
list_append(extra_project->exps,
exp_column(sql->sa, bt_name, TID,
sql_bind_localtype("oid"), CARD_MULTI, 0, 1));
@@ -1401,7 +1408,10 @@ merge_into_table(sql_query *query, dlist
}
//project columns of bt + oid
- extra_project = rel_project(sql->sa, join_rel,
rel_projections(sql, bt, NULL, 1, 0));
+ nexps = rel_projections(sql, bt, NULL, 1, 0);
+ for (node *n = nexps->h ; n ; n = n->next) /*
after going through the left outer join, a NOT NULL column may have NULL values
*/
+ set_has_nil((sql_exp*)n->data);
+ extra_project = rel_project(sql->sa, join_rel,
nexps);
list_append(extra_project->exps,
exp_column(sql->sa, bt_name, TID, sql_bind_localtype("oid"), CARD_MULTI, 0, 1));
//select bt values which are not null (they had
a match in the join)
@@ -1439,7 +1449,10 @@ merge_into_table(sql_query *query, dlist
}
//project columns of both
- extra_project = rel_project(sql->sa, join_rel,
rel_projections(sql, bt, NULL, 1, 0));
+ nexps = rel_projections(sql, bt, NULL, 1, 0);
+ for (node *n = nexps->h ; n ; n = n->next) /* after
going through the left outer join, a NOT NULL column may have NULL values */
+ set_has_nil((sql_exp*)n->data);
+ extra_project = rel_project(sql->sa, join_rel, nexps);
extra_project->exps = list_merge(extra_project->exps,
rel_projections(sql, joined, NULL, 1, 0), (fdup)NULL);
//select bt values which are null (they didn't have
match in the join)
diff --git
a/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
b/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
--- a/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
+++ b/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
@@ -25,7 +25,7 @@ stdout of test 'bool_boolean.SF-935601`
#select isnull(1);
% . # table_name
-% %1 # name
+% single_value # name
% boolean # type
% 5 # length
[ false ]
diff --git a/sql/test/subquery/Tests/subquery5.sql
b/sql/test/subquery/Tests/subquery5.sql
--- a/sql/test/subquery/Tests/subquery5.sql
+++ b/sql/test/subquery/Tests/subquery5.sql
@@ -284,6 +284,9 @@ SELECT ((SELECT i1.i NOT IN (SELECT i1.i
SELECT (SELECT 6 EXCEPT (SELECT SUM(i1.i))) IN (SELECT 1) FROM integers i1; --
OPTmergetableImplementation: !ERROR: Mergetable bailout on group input reuse in
group statement
-- NULL
+SELECT (SELECT CASE WHEN MIN(i1.i) IS NULL THEN (SELECT i2.i FROM integers i2)
ELSE MAX(i1.i) END) FROM integers i1;
+ -- 3
+
DROP FUNCTION evilfunction(INT);
DROP TABLE tbl_ProductSales;
DROP TABLE another_T;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
