Changeset: ffd1b69a0993 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/ffd1b69a0993
Branch: default
Log Message:
merge with distinct_from
diffs (266 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,6 +1,11 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Thu Mar 21 2024 Yunus Koning <yunus.kon...@monetdbsolutions.com>
+- Introduce IS [NOT] DISTINCT FROM syntax. The syntax allows two values
+ to be compared. The comparison always returns boolean FALSE or TRUE
+ never NULL.
+
* Wed Mar 6 2024 Yunus Koning <yunus.kon...@monetdbsolutions.com>
- SQL2023 feature: Introduce UNIQUE NULLS [NOT] DISTINCT syntax which
allows for NULLS to be treated as unique, i.e. a column with this
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -1831,8 +1831,8 @@ exp_bin(backend *be, sql_exp *e, stmt *l
tail_type(l), tail_type(l), F_FUNC, true, true);
assert(f);
if (is_semantics(e)) {
- if (exp_is_null(e->l) &&
exp_is_null(e->r)) {
- s = stmt_bool(be,
!is_anti(e));
+ if (exp_is_null(e->l) &&
exp_is_null(e->r) && (e->flag == cmp_equal || e->flag == cmp_notequal)) {
+ s = stmt_bool(be,
e->flag == cmp_equal ? !is_anti(e): is_anti(e));
} else {
list *args =
sa_list(sql->sa);
if (args == NULL)
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
@@ -2367,6 +2367,8 @@ rel_logical_value_exp(sql_query *query,
int quantifier = 0, need_not = 0;
sql_exp *rs = NULL, *ls;
comp_type cmp_type = compare_str2type(compare_op);
+ bool is_not_distinct_from = false;
+ bool is_distinct_from = false;
/*
* = ANY -> IN, <> ALL -> NOT( = ANY) -> NOT IN
@@ -2374,7 +2376,17 @@ rel_logical_value_exp(sql_query *query,
*/
if (n->next->next->next)
quantifier = n->next->next->next->data.i_val + 1;
- assert(quantifier == 0 || quantifier == 1 || quantifier == 2);
+ assert(quantifier == 0 || quantifier == 1 || quantifier == 2 ||
quantifier == 3 || quantifier == 4);
+
+ /* [NOT] DISTINCT FROM */
+ if (quantifier == 3) {
+ is_not_distinct_from = true;
+ quantifier = 0;
+ }
+ else if (quantifier == 4) {
+ is_distinct_from = true;
+ quantifier = 0;
+ }
if ((quantifier == 1 && cmp_type == cmp_equal) ||
(quantifier == 2 && cmp_type == cmp_notequal)) {
@@ -2405,6 +2417,15 @@ rel_logical_value_exp(sql_query *query,
rs = rel_value_exp(query, rel, ro, f|sql_farg, ek);
if (!rs)
return NULL;
+
+ if (is_distinct_from || is_not_distinct_from) {
+ if (rel_convert_types(sql, rel ? *rel : NULL, rel ?
*rel : NULL, &ls, &rs, 1, type_equal_no_any) < 0)
+ return NULL;
+ sql_exp* e = exp_compare(sql->sa, ls, rs,
is_not_distinct_from?cmp_equal:cmp_notequal);
+ set_semantics(e);
+ return e;
+ }
+
if (rs->type == e_atom)
quantifier = 0;
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -3909,6 +3909,20 @@ comparison_predicate:
append_symbol(l, $5);
append_int(l, $3);
$$ = _symbol_create_list(SQL_COMPARE, l ); }
+ | pred_exp IS NOT DISTINCT FROM pred_exp
+ { dlist *l = L();
+ append_symbol(l, $1);
+ append_string(l, sa_strdup(SA, "="));
+ append_symbol(l, $6);
+ append_int(l, 2);
+ $$ = _symbol_create_list(SQL_COMPARE, l ); }
+ | pred_exp IS DISTINCT FROM pred_exp
+ { dlist *l = L();
+ append_symbol(l, $1);
+ append_string(l, sa_strdup(SA, "="));
+ append_symbol(l, $5);
+ append_int(l, 3);
+ $$ = _symbol_create_list(SQL_COMPARE, l ); }
;
between_predicate:
diff --git a/sql/test/2024/Tests/All b/sql/test/2024/Tests/All
--- a/sql/test/2024/Tests/All
+++ b/sql/test/2024/Tests/All
@@ -1,1 +1,2 @@
groupby_primary_key_project_unique_key
+distinct_from
diff --git a/sql/test/2024/Tests/distinct_from.test
b/sql/test/2024/Tests/distinct_from.test
new file mode 100644
--- /dev/null
+++ b/sql/test/2024/Tests/distinct_from.test
@@ -0,0 +1,143 @@
+
+query I nosort
+SELECT 10 IS NOT DISTINCT FROM 20
+----
+0
+
+query I nosort
+SELECT 10 IS DISTINCT FROM 20
+----
+1
+
+
+query I nosort
+SELECT 10 IS DISTINCT FROM NULL
+----
+1
+
+query I nosort
+SELECT NULL IS DISTINCT FROM 20
+----
+1
+
+query I nosort
+SELECT 10 IS NOT DISTINCT FROM NULL
+----
+0
+
+query I nosort
+SELECT NULL IS NOT DISTINCT FROM 20
+----
+0
+
+query I nosort
+SELECT NULL IS DISTINCT FROM NULL
+----
+0
+
+query I nosort
+SELECT NULL IS NOT DISTINCT FROM NULL
+----
+1
+
+statement ok
+create table foo(s) as values (10), (20), (NULL)
+
+query I nosort
+select s IS NOT DISTINCT FROM 20 FROM foo;
+----
+0
+1
+0
+
+query I nosort
+select s IS DISTINCT FROM 20 FROM foo;
+----
+1
+0
+1
+
+query I nosort
+select s IS NOT DISTINCT FROM NULL FROM foo;
+----
+0
+0
+1
+
+query I nosort
+select s IS DISTINCT FROM NULL FROM foo;
+----
+1
+1
+0
+
+query I nosort
+select 20 IS NOT DISTINCT FROM s FROM foo;
+----
+0
+1
+0
+
+query I nosort
+select 20 IS DISTINCT FROM s FROM foo;
+----
+1
+0
+1
+
+query I nosort
+select NULL IS NOT DISTINCT FROM s FROM foo;
+----
+0
+0
+1
+
+query I nosort
+select NULL IS DISTINCT FROM s FROM foo;
+----
+1
+1
+0
+
+statement ok
+create table bar(s) as values (20), (30), (NULL)
+
+query IIII rowsort
+select foo.s, bar.s, foo.s IS DISTINCT FROM bar.s, foo.s IS NOT DISTINCT FROM
bar.s FROM foo, bar
+----
+10
+20
+1
+0
+10
+30
+1
+0
+10
+NULL
+1
+0
+20
+20
+0
+1
+20
+30
+1
+0
+20
+NULL
+1
+0
+NULL
+20
+1
+0
+NULL
+30
+1
+0
+NULL
+NULL
+0
+1
diff --git
a/sql/test/sys-schema/Tests/webExamplesComparisonFunctionsOperators.test
b/sql/test/sys-schema/Tests/webExamplesComparisonFunctionsOperators.test
--- a/sql/test/sys-schema/Tests/webExamplesComparisonFunctionsOperators.test
+++ b/sql/test/sys-schema/Tests/webExamplesComparisonFunctionsOperators.test
@@ -920,7 +920,7 @@ select 'db' not between SYMMETRIC 'db' a
----
0
-statement error
+statement ok
select 'a' IS DISTINCT FROM 'b' as tru
query I rowsort
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
