Changeset: c366d197e95d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c366d197e95d
Modified Files:
sql/common/sql_types.c
sql/server/rel_optimizer.c
sql/storage/store.c
sql/test/BugTracker-2016/Tests/case-column-when-null-Bug-6124.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
sql/test/BugTracker-2019/Tests/count-distinct.Bug-6790.stable.out
sql/test/miscellaneous/Tests/simple_selects.stable.out
Branch: oscar
Log Message:
Revising NULL semantics functions.
=,<>,between,avg,sql_neg,not_(i)like functions don't have NULL semantics, ie if
one their arguments is a NULL constant, their ouput is NULL.
least,greatest,and functions have NULL semantics, as they may not produce NULL
on certain ocasions: and(false,NULL) -> false, least(1,null) -> 1
Also make sure the "semantics" flag is propagated at func_dup and func_reset.
Added optimization when one of the paramenters for a functions without NULL
semantics is NULL, replace it with a NULL constant.
diffs (truncated from 310 to 300 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -1273,15 +1273,15 @@ sqltypeinit( sql_allocator *sa)
/* functions needed for all types */
sql_create_func(sa, "hash", "mkey", "hash", TRUE, FALSE, SCALE_FIX, 0,
LNG, 1, ANY);
sql_create_func(sa, "rotate_xor_hash", "calc", "rotate_xor_hash", TRUE,
FALSE, SCALE_NONE, 0, LNG, 3, LNG, INT, ANY);
- sql_create_func(sa, "=", "calc", "=", TRUE, FALSE, SCALE_FIX, 0, BIT,
2, ANY, ANY);
- sql_create_func(sa, "<>", "calc", "!=", TRUE, FALSE, SCALE_FIX, 0, BIT,
2, ANY, ANY);
+ sql_create_func(sa, "=", "calc", "=", FALSE, FALSE, SCALE_FIX, 0, BIT,
2, ANY, ANY);
+ sql_create_func(sa, "<>", "calc", "!=", FALSE, FALSE, SCALE_FIX, 0,
BIT, 2, ANY, ANY);
sql_create_func(sa, "isnull", "calc", "isnil", TRUE, FALSE, SCALE_FIX,
0, BIT, 1, ANY);
sql_create_func(sa, "isnotnull", "calc", "isnotnil", TRUE, FALSE,
SCALE_FIX, 0, BIT, 1, ANY);
sql_create_func(sa, ">", "calc", ">", FALSE, FALSE, SCALE_FIX, 0, BIT,
2, ANY, ANY);
sql_create_func(sa, ">=", "calc", ">=", FALSE, FALSE, SCALE_FIX, 0,
BIT, 2, ANY, ANY);
sql_create_func(sa, "<", "calc", "<", FALSE, FALSE, SCALE_FIX, 0, BIT,
2, ANY, ANY);
sql_create_func(sa, "<=", "calc", "<=", FALSE, FALSE, SCALE_FIX, 0,
BIT, 2, ANY, ANY);
- sql_create_func(sa, "between", "calc", "between", TRUE, FALSE,
SCALE_FIX, 0, BIT, 8, ANY, ANY, ANY, BIT, BIT, BIT, BIT, BIT);
+ sql_create_func(sa, "between", "calc", "between", FALSE, FALSE,
SCALE_FIX, 0, BIT, 8, ANY, ANY, ANY, BIT, BIT, BIT, BIT, BIT);
sql_create_aggr(sa, "zero_or_one", "aggr", "zero_or_one", TRUE, ANY, 1,
ANY);
sql_create_aggr(sa, "all", "aggr", "all", TRUE, ANY, 1, ANY);
sql_create_aggr(sa, "null", "aggr", "null", TRUE, BIT, 1, ANY);
@@ -1304,8 +1304,8 @@ sqltypeinit( sql_allocator *sa)
sql_create_aggr(sa, "max", "aggr", "max", FALSE, ANY, 1, ANY);
sql_create_func(sa, "sql_min", "calc", "min", FALSE, FALSE, SCALE_FIX,
0, ANY, 2, ANY, ANY);
sql_create_func(sa, "sql_max", "calc", "max", FALSE, FALSE, SCALE_FIX,
0, ANY, 2, ANY, ANY);
- sql_create_func(sa, "least", "calc", "min_no_nil", FALSE, FALSE,
SCALE_FIX, 0, ANY, 2, ANY, ANY);
- sql_create_func(sa, "greatest", "calc", "max_no_nil", FALSE, FALSE,
SCALE_FIX, 0, ANY, 2, ANY, ANY);
+ sql_create_func(sa, "least", "calc", "min_no_nil", TRUE, FALSE,
SCALE_FIX, 0, ANY, 2, ANY, ANY);
+ sql_create_func(sa, "greatest", "calc", "max_no_nil", TRUE, FALSE,
SCALE_FIX, 0, ANY, 2, ANY, ANY);
sql_create_func(sa, "ifthenelse", "calc", "ifthenelse", TRUE, FALSE,
SCALE_FIX, 0, ANY, 3, BIT, ANY, ANY);
/* nullif and coalesce don't have a backend implementation */
sql_create_func(sa, "nullif", "", "", TRUE, FALSE, SCALE_FIX, 0, ANY,
2, ANY, ANY);
@@ -1346,16 +1346,16 @@ sqltypeinit( sql_allocator *sa)
#if 0
/* prod for decimals introduce errors in the output scales */
t = decimals; /* BTE */
- sql_create_aggr(sa, "prod", "aggr", "prod", TRUE, LargestDEC, 1, *(t));
+ sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestDEC, 1, *(t));
t++; /* SHT */
- sql_create_aggr(sa, "prod", "aggr", "prod", TRUE, LargestDEC, 1, *(t));
+ sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestDEC, 1, *(t));
t++; /* INT */
- sql_create_aggr(sa, "prod", "aggr", "prod", TRUE, LargestDEC, 1, *(t));
+ sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestDEC, 1, *(t));
t++; /* LNG */
- sql_create_aggr(sa, "prod", "aggr", "prod", TRUE, LargestDEC, 1, *(t));
+ sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestDEC, 1, *(t));
#ifdef HAVE_HGE
t++; /* HGE */
- sql_create_aggr(sa, "prod", "aggr", "prod", TRUE, LargestDEC, 1, *(t));
+ sql_create_aggr(sa, "prod", "aggr", "prod", FALSE, LargestDEC, 1, *(t));
#endif
#endif
@@ -1371,31 +1371,31 @@ sqltypeinit( sql_allocator *sa)
sql_create_aggr(sa, "sum", "aggr", "sum", FALSE, SECINT, 1, SECINT);
/* do DBL first so that it is chosen as cast destination for
* unknown types */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, DBL);
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, BTE);
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, SHT);
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, INT);
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, LNG);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, DBL);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, BTE);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, SHT);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, INT);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, LNG);
#ifdef HAVE_HGE
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, HGE);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, HGE);
#endif
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, DBL, 1, FLT);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, DBL, 1, FLT);
t = decimals; /* BTE */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, *(t), 1, *(t));
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, *(t), 1, *(t));
t++; /* SHT */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, *(t), 1, *(t));
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, *(t), 1, *(t));
t++; /* INT */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, *(t), 1, *(t));
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, *(t), 1, *(t));
t++; /* LNG */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, *(t), 1, *(t));
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, *(t), 1, *(t));
#ifdef HAVE_HGE
t++; /* HGE */
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, *(t), 1, *(t));
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, *(t), 1, *(t));
#endif
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, MONINT, 1, MONINT);
- sql_create_aggr(sa, "avg", "aggr", "avg", TRUE, SECINT, 1, SECINT);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, MONINT, 1, MONINT);
+ sql_create_aggr(sa, "avg", "aggr", "avg", FALSE, SECINT, 1, SECINT);
sql_create_aggr(sa, "count_no_nil", "aggr", "count_no_nil", TRUE, LNG,
0);
sql_create_aggr(sa, "count", "aggr", "count", TRUE, LNG, 1, ANY);
@@ -1591,11 +1591,10 @@ sqltypeinit( sql_allocator *sa)
sql_create_analytic(sa, "avg", "sql", "avg", SCALE_NONE, MONINT, 1,
MONINT);
sql_create_analytic(sa, "avg", "sql", "avg", SCALE_NONE, SECINT, 1,
SECINT);
-
sql_create_analytic(sa, "listagg", "sql", "str_group_concat",
SCALE_NONE, STR, 1, STR);
sql_create_analytic(sa, "listagg", "sql", "str_group_concat",
SCALE_NONE, STR, 2, STR, STR);
- sql_create_func(sa, "and", "calc", "and", FALSE, FALSE, SCALE_FIX, 0,
BIT, 2, BIT, BIT);
+ sql_create_func(sa, "and", "calc", "and", TRUE, FALSE, SCALE_FIX, 0,
BIT, 2, BIT, BIT);
sql_create_func(sa, "or", "calc", "or", TRUE, FALSE, SCALE_FIX, 0,
BIT, 2, BIT, BIT);
sql_create_func(sa, "xor", "calc", "xor", FALSE, FALSE, SCALE_FIX, 0,
BIT, 2, BIT, BIT);
sql_create_func(sa, "not", "calc", "not", FALSE, FALSE, SCALE_FIX, 0,
BIT, 1, BIT);
@@ -1640,7 +1639,7 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "left_shift", "calc", "<<", FALSE,
FALSE, SCALE_FIX, 0, *t, 2, *t, INT);
sql_create_func(sa, "right_shift", "calc", ">>", FALSE,
FALSE, SCALE_FIX, 0, *t, 2, *t, INT);
}
- sql_create_func(sa, "sql_neg", "calc", "-", TRUE, FALSE, INOUT,
0, *t, 1, *t);
+ sql_create_func(sa, "sql_neg", "calc", "-", FALSE, FALSE,
INOUT, 0, *t, 1, *t);
sql_create_func(sa, "abs", "calc", "abs", FALSE, FALSE,
SCALE_FIX, 0, *t, 1, *t);
sql_create_func(sa, "sign", "calc", "sign", FALSE, FALSE,
SCALE_NONE, 0, BTE, 1, *t);
/* scale fixing for all numbers */
@@ -1814,12 +1813,12 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "ilike", "algebra", "ilike", FALSE,
SCALE_NONE, 0, BIT, 2, *t, *t);
sql_create_func3(sa, "ilike", "algebra", "ilike", FALSE,
SCALE_NONE, 0, BIT, 3, *t, *t, *t);
*/
- sql_create_func(sa, "not_like", "algebra", "not_like", TRUE,
FALSE, SCALE_NONE, 0, BIT, 2, *t, *t);
- sql_create_func(sa, "not_like", "algebra", "not_like", TRUE,
FALSE, SCALE_NONE, 0, BIT, 3, *t, *t, *t);
- sql_create_func(sa, "not_ilike", "algebra", "not_ilike", TRUE,
FALSE, SCALE_NONE, 0, BIT, 2, *t, *t);
- sql_create_func(sa, "not_ilike", "algebra", "not_ilike", TRUE,
FALSE, SCALE_NONE, 0, BIT, 3, *t, *t, *t);
+ sql_create_func(sa, "not_like", "algebra", "not_like", FALSE,
FALSE, SCALE_NONE, 0, BIT, 2, *t, *t);
+ sql_create_func(sa, "not_like", "algebra", "not_like", FALSE,
FALSE, SCALE_NONE, 0, BIT, 3, *t, *t, *t);
+ sql_create_func(sa, "not_ilike", "algebra", "not_ilike", FALSE,
FALSE, SCALE_NONE, 0, BIT, 2, *t, *t);
+ sql_create_func(sa, "not_ilike", "algebra", "not_ilike", FALSE,
FALSE, SCALE_NONE, 0, BIT, 3, *t, *t, *t);
- sql_create_func(sa, "patindex", "pcre", "patindex", TRUE,
FALSE, SCALE_NONE, 0, INT, 2, *t, *t);
+ sql_create_func(sa, "patindex", "pcre", "patindex", FALSE,
FALSE, SCALE_NONE, 0, INT, 2, *t, *t);
sql_create_func(sa, "truncate", "str", "stringleft", FALSE,
FALSE, SCALE_NONE, 0, *t, 2, *t, INT);
sql_create_func(sa, "concat", "calc", "+", FALSE, FALSE,
DIGITS_ADD, 0, *t, 2, *t, *t);
sql_create_func(sa, "ascii", "str", "ascii", FALSE, FALSE,
SCALE_NONE, 0, INT, 1, *t);
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
@@ -2899,51 +2899,47 @@ exp_simplify_math( mvc *sql, sql_exp *e,
if (list_length(l) < 1)
return e;
- le = l->h->data;
- if (!exp_subtype(le) ||
(!EC_COMPUTE(exp_subtype(le)->type->eclass) && exp_subtype(le)->type->eclass !=
EC_DEC))
- return e;
-
- if (!f->func->s && list_length(l) == 2) {
+ /* if the function has no null semantics we can return NULL if
one of the arguments is NULL */
+ if (!f->func->semantics && f->func->type != F_PROC) {
+ for (node *n = l->h ; n ; n = n->next) {
+ sql_exp *arg = n->data;
+
+ if (exp_is_atom(arg) && exp_is_null(sql, arg)) {
+ sql_exp *ne = exp_null(sql->sa,
exp_subtype(e));
+ (*changes)++;
+ if (exp_name(e))
+ exp_prop_alias(sql->sa, ne, e);
+ return ne;
+ }
+ }
+ }
+ if (!f->func->s && list_length(l) == 2 && strstr(f->func->imp,
"_no_nil") != NULL) {
sql_exp *le = l->h->data;
sql_exp *re = l->h->next->data;
- sql_subtype *et = exp_subtype(e);
-
- /* if one argument is NULL, return it, EXCEPT
- * if "_no_nil" is in the name of the
+
+ /* if "_no_nil" is in the name of the
* implementation function (currently either
* min_no_nil or max_no_nil), in which case we
- * ignore the NULL and return the other
- * value */
-
- /* for both nullif and coalesce don't rewrite the NULL
handling */
- if (f && f->func && f->func->imp &&
strcmp(f->func->imp, "") == 0)
- return e;
+ * ignore the NULL and return the other value */
if (exp_is_atom(le) && exp_is_null(sql, le)) {
(*changes)++;
- if (f && f->func && f->func->imp &&
strstr(f->func->imp, "_no_nil") != NULL) {
- if (exp_name(e))
- exp_prop_alias(sql->sa, re, e);
- return re;
- }
- le = exp_null(sql->sa, et);
if (exp_name(e))
- exp_prop_alias(sql->sa, le, e);
- return le;
+ exp_prop_alias(sql->sa, re, e);
+ return re;
}
if (exp_is_atom(re) && exp_is_null(sql, re)) {
(*changes)++;
- if (f && f->func && f->func->imp &&
strstr(f->func->imp, "_no_nil") != NULL) {
- if (exp_name(e))
- exp_prop_alias(sql->sa, le, e);
- return le;
- }
- re = exp_null(sql->sa, et);
if (exp_name(e))
- exp_prop_alias(sql->sa, re, e);
- return re;
- }
- }
+ exp_prop_alias(sql->sa, le, e);
+ return le;
+ }
+ }
+
+ le = l->h->data;
+ if (!EC_COMPUTE(exp_subtype(le)->type->eclass) &&
exp_subtype(le)->type->eclass != EC_DEC)
+ return e;
+
if (!f->func->s && !strcmp(f->func->base.name, "sql_mul") &&
list_length(l) == 2) {
sql_exp *le = l->h->data;
sql_exp *re = l->h->next->data;
diff --git a/sql/storage/store.c b/sql/storage/store.c
--- a/sql/storage/store.c
+++ b/sql/storage/store.c
@@ -3660,6 +3660,7 @@ func_dup(sql_trans *tr, int flags, sql_f
f->ops = list_new(sa, of->ops->destroy);
f->fix_scale = of->fix_scale;
f->system = of->system;
+ f->semantics = of->semantics;
for (n=of->ops->h; n; n = n->next)
list_append(f->ops, arg_dup(newFlagSet(flags)?tr->parent:tr, s,
n->data));
if (of->res) {
@@ -4782,7 +4783,7 @@ reset_func(sql_trans *tr, sql_func *ff,
ff->res = pff->res;
ff->fix_scale = pff->fix_scale;
ff->system = pff->system;
- ff->ops = pff->ops;
+ ff->semantics = pff->semantics;
ff->s = find_sql_schema(tr, pff->s->base.name);
ff->sa = tr->sa;
}
diff --git
a/sql/test/BugTracker-2016/Tests/case-column-when-null-Bug-6124.stable.out
b/sql/test/BugTracker-2016/Tests/case-column-when-null-Bug-6124.stable.out
--- a/sql/test/BugTracker-2016/Tests/case-column-when-null-Bug-6124.stable.out
+++ b/sql/test/BugTracker-2016/Tests/case-column-when-null-Bug-6124.stable.out
@@ -88,7 +88,7 @@ stdout of test 'case-column-when-null-Bu
[ 2, 1 ]
[ NULL, 0 ]
#SELECT this_column, (CASE this_column WHEN NULL THEN 0 ELSE 1 END) AS
new_column FROM table_two;
-% sys.table_two, sys. # table_name
+% sys.table_two, . # table_name
% this_column, new_column # name
% int, tinyint # type
% 1, 1 # length
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
@@ -73,7 +73,7 @@ stdout of test 'sqlitelogictest-cast-nul
[ NULL ]
#SELECT ALL CAST ( col1 / - - ( + CAST ( NULL AS INTEGER ) ) AS BIGINT ) col0,
# CAST ( 39 + + + CAST ( NULL AS INTEGER ) + + - 10 + col1 / - col0
AS BIGINT ) AS col1 FROM tab2;
-% sys., . # table_name
+% ., . # table_name
% col0, col1 # name
% bigint, bigint # type
% 1, 1 # length
diff --git a/sql/test/BugTracker-2019/Tests/count-distinct.Bug-6790.stable.out
b/sql/test/BugTracker-2019/Tests/count-distinct.Bug-6790.stable.out
--- a/sql/test/BugTracker-2019/Tests/count-distinct.Bug-6790.stable.out
+++ b/sql/test/BugTracker-2019/Tests/count-distinct.Bug-6790.stable.out
@@ -90,14 +90,14 @@ stdout of test 'count-distinct.Bug-6790`
% 4 # length
[ 3823 ]
#SELECT CAST(72 + - + COUNT ( * ) - - - COUNT ( DISTINCT + ( - 2 ) ) * + + 57
AS BIGINT) FROM tab2;
-% . # table_name
+% .%3 # table_name
% %3 # name
% bigint # type
% 2 # length
[ 12 ]
#SELECT DISTINCT - 37, - COUNT ( DISTINCT - + 96 ) col1 FROM tab2;
% ., . # table_name
-% %2, col1 # name
+% %3, col1 # name
% tinyint, bigint # type
% 3, 2 # length
[ -37, -1 ]
@@ -118,7 +118,7 @@ stdout of test 'count-distinct.Bug-6790`
[ NULL, 3886 ]
[ NULL, 1173 ]
#SELECT DISTINCT + 14 AS col2, + col0 - - CAST ( NULL AS INTEGER ) AS col2
FROM tab2 AS cor0;
-% ., sys. # table_name
+% ., . # table_name
% col2, col2 # name
% tinyint, bigint # type
% 2, 1 # length
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.out
b/sql/test/miscellaneous/Tests/simple_selects.stable.out
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.out
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
