github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_coalesce.result

CREATE TABLE t1 (a BIGINT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9;
a
SELECT * FROM t1 WHERE coalesce(a)=0.9;
a
SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9);
a
SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9;
a
SELECT * FROM t1 WHERE a=0.9;
a
SELECT * FROM t1 WHERE a IN (0.8,0.9);
a
DROP TABLE t1;
CREATE TABLE t1 (EMPNUM INT);
INSERT INTO t1 VALUES (0), (2);
CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));
INSERT INTO t2 VALUES (0.0), (9.0);
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
CEMPNUM	EMPMUM1	EMPNUM2
0.00	0	0.00
2.00	2	null
drop table t1;
drop table t2;
CREATE TABLE t0(c0 BIGINT UNSIGNED);
INSERT INTO t0(c0) VALUES(NULL);
SELECT * FROM t0 WHERE CAST(COALESCE(t0.c0, -1) AS UNSIGNED);
Data truncation: data out of range: data type uint64, 
SELECT * FROM t0 WHERE CAST(IFNULL(t0.c0, -1) AS UNSIGNED);
Data truncation: data out of range: data type uint64, 
SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) IS TRUE FROM t0;
Data truncation: data out of range: data type uint64, 
SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) FROM t0;
Data truncation: data out of range: data type uint64, 
DROP TABLE t0;
CREATE TABLE t1 (a char(10), b INT);
INSERT INTO t1 VALUES ('', 0);
SELECT COALESCE(a) = COALESCE(b) FROM t1;
invalid argument cast to int, bad value 
DROP TABLE t1;
CREATE TABLE t1 ( a INTEGER, b varchar(255) );
INSERT INTO t1 VALUES (1,'z');
INSERT INTO t1 VALUES (2,'y');
INSERT INTO t1 VALUES (3,'x');
SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a');
min_b
x
y
z
SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a');
min_b
x
y
z
SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a') DESC;
min_b
z
y
x
SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a') DESC;
min_b
z
y
x
DROP TABLE t1;
create table t1 (a bigint unsigned);
insert into t1 select (if(1, 9223372036854775808, 1));
Data truncation: data out of range: data type int64, 
insert into t1 select (case when 1 then 9223372036854775808 else 1 end);
Data truncation: data out of range: data type int64, 
insert into t1 select (coalesce(9223372036854775808, 1));
Data truncation: data out of range: data type int64, 
select * from t1;
a
drop table t1;
select if(1, cast(1111111111111111111 as unsigned), 1) i,case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co;
i	c	co
1111111111111111111	1111111111111111111	1111111111111111111
CREATE TABLE ot (col_int_nokey int(11), col_varchar_nokey varchar(1));
INSERT INTO ot VALUES (1,'x');
CREATE TABLE it (col_int_key int(11), col_varchar_key varchar(1));
INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN(SELECT col_varchar_key FROM it WHERE coalesce(col_int_nokey, 1) );
col_int_nokey
1
drop table ot;
drop table it;
CREATE TABLE ot1(a INT);
CREATE TABLE ot2(a INT);
CREATE TABLE ot3(a INT);
CREATE TABLE it1(a INT);
CREATE TABLE it2(a INT);
CREATE TABLE it3(a INT);
INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
INSERT INTO ot2 VALUES(0),(2),(4),(6);
INSERT INTO ot3 VALUES(0),(3),(6);
INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
INSERT INTO it2 VALUES(0),(2),(4),(6);
INSERT INTO it3 VALUES(0),(3),(6);
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3);
a	a
0	0
1	null
3	null
5	null
6	6
7	null
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
WHERE COALESCE(ot2.a,0) IN (SELECT a+0 FROM it3);
a	a
0	0
1	null
3	null
5	null
6	6
7	null
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
LEFT JOIN ot3 ON ot1.a=ot3.a
WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3);
a	a	a
0	0	0
1	null	null
3	null	3
5	null	null
6	6	6
7	null	null
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
LEFT JOIN ot3 ON ot1.a=ot3.a
WHERE COALESCE(ot3.a,0) IN (SELECT a FROM it3);
a	a	a
0	0	0
1	null	null
2	2	null
3	null	3
4	4	null
5	null	null
6	6	6
7	null	null
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
LEFT JOIN ot3 ON ot2.a=ot3.a
WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3);
a	a	a
0	0	0
1	null	null
3	null	null
5	null	null
6	6	6
7	null	null
SELECT *
FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a
LEFT JOIN ot3 ON ot2.a=ot3.a
WHERE COALESCE(ot3.a,0) IN (SELECT a FROM it3);
a	a	a
0	0	0
1	null	null
2	2	null
3	null	null
4	4	null
5	null	null
6	6	6
7	null	null
drop table ot1;
drop table ot2;
drop table ot3;
drop table it1;
drop table it2;
drop table it3;
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIMESTAMP, d DATE);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '2001-01-01 00:00:00.567', '2002-01-01');
SELECT distinct COALESCE(dt2, t3) FROM t1;
COALESCE(dt2, t3)
2001-01-01 00:00:00.120000000
SELECT CONCAT_WS(",", COALESCE(dt2, t3)) FROM t1;
CONCAT_WS(",", COALESCE(dt2, t3))
2001-01-01 00:00:00.12
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2000-01-01');
SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
cast(coalesce(a, a) as signed)
10957
SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
CAST(COALESCE(a,a) AS CHAR)
2000-01-01
SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
invalid argument operator cast, bad value [DATE DECIMAL128]
SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
CAST(COALESCE(a,a) AS DATETIME(6))
2000-01-01 00:00:00
SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
CAST(COALESCE(a,a) AS TIME(6))
00:00:00
SELECT ROUND(COALESCE(a,a)) FROM t1;
invalid argument function round, bad value [DATE]
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
cast(coalesce(a, a) as signed)
946684800
SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
CAST(COALESCE(a,a) AS CHAR)
2000-01-01 00:00:00
SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
invalid argument operator cast, bad value [DATETIME DECIMAL128]
SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
CAST(COALESCE(a,a) AS DATETIME(6))
2000-01-01 00:00:00
SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
CAST(COALESCE(a,a) AS TIME(6))
00:00:00
SELECT ROUND(COALESCE(a,a)) FROM t1;
invalid argument function round, bad value [DATETIME]
DROP TABLE t1;
select coalesce(null);
coalesce(null)
null
SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');
COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com')
W3Schools.com
SELECT COALESCE(1)+COALESCE(1);
COALESCE(1)+COALESCE(1)
2
drop table if exists t1;
create table t1(a datetime);
INSERT INTO t1 VALUES (NULL), ('2001-01-01 00:00:00.12'), ('2002-01-01 00:00:00.567');
select a from t1 group by a having COALESCE(a)<"2002-01-01";
a
2001-01-01 00:00:00
drop table t1;
drop table if exists t1;
drop table if exists t2;
create table t1(a INT,  b varchar(255));
create table t2(a INT,  b varchar(255));
insert into t1 values(1, "你好"), (3, "再见");
insert into t2 values(2, "日期时间"), (4, "明天");
SELECT t1.a, t2.a FROM t1 JOIN t2 ON (length(COALESCE(t1.b)) = length(COALESCE(t2.b)));
a	a
3	4
1	4
drop table t1;
drop table t2;
SELECT COALESCE(NULL, NULL, NULL, CAST('{"_id":"192312412512"}' AS JSON), NULL, CAST('{"_id":"192312412513"}' AS JSON));
coalesce(null, null, null, cast({"_id":"192312412512"} as json), null, cast({"_id":"192312412513"} as json))
{"_id": "192312412512"}
create table t1(a INT,  b json);
create table t2(a INT,  b json);
insert into t1 values(1, CAST('{"_id":"192312412512"}' AS JSON)), (3, CAST('{"_id":"192312412513"}' AS JSON));
insert into t2 values(2, CAST('{"_id":"192312412514"}' AS JSON)), (4, CAST('{"_id":"192312412515"}' AS JSON));
select * from t1 union all select * from t2;
a    b
1    {"_id": "192312412512"}
3    {"_id": "192312412513"}
2    {"_id": "192312412514"}
4    {"_id": "192312412515"}
drop table t1;
drop table t2;
SELECT COALESCE(NULL, NULL, NULL, CAST('test' AS BLOB), NULL, CAST('1234589002' AS BLOB));
coalesce(null, null, null, cast(test as blob), null, cast(1234589002 as blob))
test
DROP table if exists t1;
DROP table if exists t2;
CREATE TABLE t1 (a INT, s BLOB);
INSERT INTO t1 VALUES (1, 'test');
CREATE TABLE t2 (b INT, s BLOB);
INSERT INTO t2 VALUES (2, '1234589002');
select * from t1 union all select * from t2;
a    s
1    test
2    1234589002
drop table t1;
drop table t2;
SELECT COALESCE(NULL, NULL, NULL, CAST('abcdef' AS text), NULL, CAST('1234589002' AS text));
coalesce(null, null, null, cast(abcdef as text), null, cast(1234589002 as text))
abcdef
DROP table if exists t1;
DROP table if exists t2;
CREATE TABLE t1 (a INT, s text);
INSERT INTO t1 VALUES (1, 'abcdef');
CREATE TABLE t2 (b INT, s text);
INSERT INTO t2 VALUES (2, 'abcdefgh');
select * from t1 union all select * from t2;
a    s
1    abcdef
2    abcdefgh
drop table t1;
drop table t2;