Changeset: bf7f59787fe9 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/bf7f59787fe9
Branch: Jun2023
Log Message:
Merge heads.
diffs (253 lines):
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
@@ -105,9 +105,6 @@ select ifthenelse(('a' = 'b'), 1, 2) as
----
2
-statement error
-select ifnull('aa', 'b') as aa
-
query T rowsort
select ifthenelse(('aa' IS NULL), cast('b' as varchar(2)), 'aa') as aa
----
@@ -122,16 +119,41 @@ statement error
select ifnull('aa', 'b') as aa
statement error
-select ifnull(null, 'b') as b
+select ifnull(null, 'ams') as ams
statement error
select ifnull(null, null) as nul
statement error
-select ifnull('aa', null) as nul
+select ifnull('aa', null) as aa
statement error
-select ifnull(true, null) as nul
+select ifnull(true, null) as tru
+
+query T rowsort
+select {fn IFNULL('aa', 'b')} as aa
+----
+aa
+
+query T rowsort
+select {fn IFNULL(NULL, 'ams')} as ams
+----
+ams
+
+query T rowsort
+select {fn IFNULL(NULL, NULL)} as nul
+----
+NULL
+
+query T rowsort
+select {fn IFNULL('aa', NULL)} as aa
+----
+aa
+
+query I rowsort
+select {fn IFNULL(TRUE, NULL)} as tru
+----
+1
statement ok
drop table if exists t6933
@@ -152,6 +174,31 @@ statement error
select c, i, ifnull(c, cast(i as char)) as "ifnull(c,i)" from t6933
query ITITT rowsort
+select i, c, {fn ifnull(i, 2*3)} as "ifnull(i,2*3)", { fn ifnull(c, 'has
null') } as "ifnull(i,'x')", { fn ifnull(i, c)} as "ifnull(i,c)" from t6933
+----
+1
+aa1
+1
+aa1
+1
+2
+NULL
+2
+has null
+2
+NULL
+NULL
+6
+has null
+NULL
+NULL
+bb2
+6
+bb2
+bb2
+
+
+query ITITT rowsort
select i, c, coalesce(i, 2*3) as "ifnull(i,2*3)", coalesce(c, 'has null') as
"ifnull(i,'x')", coalesce(i, c) as "ifnull(i,c)" from t6933
----
1
@@ -200,6 +247,22 @@ bb2
bb2
query TIT rowsort
+select c, i, {fn IFNULL(c, i) } as "IFNULL(c,i)" from t6933
+----
+NULL
+2
+2
+NULL
+NULL
+NULL
+aa1
+1
+aa1
+bb2
+NULL
+bb2
+
+query TIT rowsort
select c, i, coalesce(c, i) as "ifnull(c,i)" from t6933
----
NULL
@@ -215,6 +278,28 @@ bb2
NULL
bb2
+statement error
+select c, i, {fn ifnull(c, {fn cast(i as char)})} as "ifnull(c,i)" from t6933
+
+statement error
+select c, i, {fn ifnull(c, {fn cast(i as SQL_char)})} as "ifnull(c,i)" from
t6933
+
+query TIT rowsort
+select c, i, {fn ifnull(c, {fn convert(i, SQL_char)})} as "ifnull(c,i)" from
t6933
+----
+NULL
+2
+2
+NULL
+NULL
+NULL
+aa1
+1
+aa1
+bb2
+NULL
+bb2
+
query TIT rowsort
select c, i, coalesce(c, cast(i as char)) as "ifnull(c,i)" from t6933
----
@@ -351,6 +436,19 @@ select isnull('aa') as fals
----
0
+statement error
+select like('aabbccdd', '%bc_d%', '', false) as tru
+
+query I rowsort
+select "like"('aabbccdd', '%bc_d%', '', false) as tru
+----
+1
+
+query I rowsort
+select "like"('aabbccdd', '%bc_d%', '', true) as tru
+----
+1
+
query I rowsort
select not_like('abc', '%b%', '', false) as fals
----
@@ -558,19 +656,6 @@ select not_like('aabbccdd', R'%bc\_d%',
----
1
-statement error
-select like('aabbccdd', '%bc_d%', '', false) as tru
-
-query I rowsort
-select "like"('aabbccdd', '%bc_d%', '', false) as tru
-----
-1
-
-query I rowsort
-select "like"('aabbccdd', '%bc_d%', '', true) as tru
-----
-1
-
query T rowsort
select coalesce(1, 'b', null)
----
diff --git
a/sql/test/sys-schema/Tests/webExamplesLogicalFunctionsOperators.test
b/sql/test/sys-schema/Tests/webExamplesLogicalFunctionsOperators.test
--- a/sql/test/sys-schema/Tests/webExamplesLogicalFunctionsOperators.test
+++ b/sql/test/sys-schema/Tests/webExamplesLogicalFunctionsOperators.test
@@ -129,6 +129,16 @@ 1
0
0
+query II rowsort
+select distinct a, a = NULL as "a = NULL" from b
+----
+0
+NULL
+1
+NULL
+NULL
+NULL
+
query IIIIIIII rowsort
select b.*, a and b, "and"(a,b), a or b, "or"(a,b), not a, "xor"(a,b) from b
----
@@ -205,6 +215,12 @@ NULL
NULL
NULL
+statement error
+select a, b, "all"(a, b, a) as "all(a, b, a)", "all"(a, b, b) as "all(a, b,
b)" from b
+
+statement error
+select a, b, "any"(a, b, a) as "any(a, b, a)", "any"(a, b, b) as "any(a, b,
b)" from b
+
query III rowsort
select a, b, coalesce(a, b) as "coalesce(a,b)" from b
----
@@ -236,6 +252,37 @@ NULL
NULL
NULL
+query III rowsort
+select a, b, {fn IFNULL(a, b)} as "IFNULL(a,b)" from b
+----
+0
+0
+0
+0
+1
+0
+0
+NULL
+0
+1
+0
+1
+1
+1
+1
+1
+NULL
+1
+NULL
+0
+0
+NULL
+1
+1
+NULL
+NULL
+NULL
+
statement ok
create view b3 as select a,b,true as c from b union all select a,b,false as c
from b union all select a,b,null as c from b
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
