github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_coalesce.test (about) 1 2 3 4 # WHERE clause, 比较运算 5 CREATE TABLE t1 (a BIGINT); 6 INSERT INTO t1 VALUES (1); 7 SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; 8 SELECT * FROM t1 WHERE coalesce(a)=0.9; 9 SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); 10 SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; 11 SELECT * FROM t1 WHERE a=0.9; 12 SELECT * FROM t1 WHERE a IN (0.8,0.9); 13 DROP TABLE t1; 14 15 16 # SELECT clause 17 CREATE TABLE t1 (EMPNUM INT); 18 INSERT INTO t1 VALUES (0), (2); 19 CREATE TABLE t2 (EMPNUM DECIMAL (4, 2)); 20 INSERT INTO t2 VALUES (0.0), (9.0); 21 SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, 22 t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2 23 FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; 24 drop table t1; 25 drop table t2; 26 27 # 嵌套 28 #0.5的IFNULL暂不支持 29 CREATE TABLE t0(c0 BIGINT UNSIGNED); 30 INSERT INTO t0(c0) VALUES(NULL); 31 SELECT * FROM t0 WHERE CAST(COALESCE(t0.c0, -1) AS UNSIGNED); 32 SELECT * FROM t0 WHERE CAST(IFNULL(t0.c0, -1) AS UNSIGNED); 33 SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) IS TRUE FROM t0; 34 SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) FROM t0; 35 DROP TABLE t0; 36 37 # Comparison 38 CREATE TABLE t1 (a char(10), b INT); 39 INSERT INTO t1 VALUES ('', 0); 40 SELECT COALESCE(a) = COALESCE(b) FROM t1; 41 DROP TABLE t1; 42 43 # ORDER BY clause, DATE TYPE 44 CREATE TABLE t1 ( a INTEGER, b varchar(255) ); 45 INSERT INTO t1 VALUES (1,'z'); 46 INSERT INTO t1 VALUES (2,'y'); 47 INSERT INTO t1 VALUES (3,'x'); 48 SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a'); 49 SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a'); 50 SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a') DESC; 51 SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a') DESC; 52 DROP TABLE t1; 53 54 # INSERT Clause, DATE TYPE 55 create table t1 (a bigint unsigned); 56 insert into t1 select (if(1, 9223372036854775808, 1)); 57 insert into t1 select (case when 1 then 9223372036854775808 else 1 end); 58 insert into t1 select (coalesce(9223372036854775808, 1)); 59 select * from t1; 60 drop table t1; 61 62 # Case When Clause 63 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; 64 65 # IN Subquery 66 CREATE TABLE ot (col_int_nokey int(11), col_varchar_nokey varchar(1)); 67 INSERT INTO ot VALUES (1,'x'); 68 CREATE TABLE it (col_int_key int(11), col_varchar_key varchar(1)); 69 INSERT INTO it VALUES (NULL,'x'), (NULL,'f'); 70 SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN(SELECT col_varchar_key FROM it WHERE coalesce(col_int_nokey, 1) ); 71 drop table ot; 72 drop table it; 73 74 #WHERE 75 CREATE TABLE ot1(a INT); 76 CREATE TABLE ot2(a INT); 77 CREATE TABLE ot3(a INT); 78 CREATE TABLE it1(a INT); 79 CREATE TABLE it2(a INT); 80 CREATE TABLE it3(a INT); 81 INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); 82 INSERT INTO ot2 VALUES(0),(2),(4),(6); 83 INSERT INTO ot3 VALUES(0),(3),(6); 84 INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); 85 INSERT INTO it2 VALUES(0),(2),(4),(6); 86 INSERT INTO it3 VALUES(0),(3),(6); 87 SELECT * 88 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 89 WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3); 90 SELECT * 91 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 92 WHERE COALESCE(ot2.a,0) IN (SELECT a+0 FROM it3); 93 SELECT * 94 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 95 LEFT JOIN ot3 ON ot1.a=ot3.a 96 WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3); 97 SELECT * 98 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 99 LEFT JOIN ot3 ON ot1.a=ot3.a 100 WHERE COALESCE(ot3.a,0) IN (SELECT a FROM it3); 101 SELECT * 102 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 103 LEFT JOIN ot3 ON ot2.a=ot3.a 104 WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3); 105 SELECT * 106 FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a 107 LEFT JOIN ot3 ON ot2.a=ot3.a 108 WHERE COALESCE(ot3.a,0) IN (SELECT a FROM it3); 109 drop table ot1; 110 drop table ot2; 111 drop table ot3; 112 drop table it1; 113 drop table it2; 114 drop table it3; 115 116 #DATATYPE, distinct 117 CREATE TABLE t1 (dt2 DATETIME(2), t3 TIMESTAMP, d DATE); 118 INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '2001-01-01 00:00:00.567', '2002-01-01'); 119 SELECT distinct COALESCE(dt2, t3) FROM t1; 120 SELECT CONCAT_WS(",", COALESCE(dt2, t3)) FROM t1; 121 DROP TABLE t1; 122 123 #SELECT 嵌套 124 CREATE TABLE t1 (a DATE); 125 INSERT INTO t1 VALUES ('2000-01-01'); 126 SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1; 127 SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1; 128 SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1; 129 SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1; 130 131 SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1; 132 133 SELECT ROUND(COALESCE(a,a)) FROM t1; 134 DROP TABLE t1; 135 CREATE TABLE t1 (a DATETIME); 136 INSERT INTO t1 VALUES ('2000-01-01 00:00:00'); 137 SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1; 138 SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1; 139 SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1; 140 SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1; 141 142 SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1; 143 144 SELECT ROUND(COALESCE(a,a)) FROM t1; 145 DROP TABLE t1; 146 147 #null 148 select coalesce(null); 149 150 #EXTRME VALUE 151 SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com'); 152 153 #算术操作 154 SELECT COALESCE(1)+COALESCE(1); 155 156 #HAVING 157 drop table if exists t1; 158 create table t1(a datetime); 159 INSERT INTO t1 VALUES (NULL), ('2001-01-01 00:00:00.12'), ('2002-01-01 00:00:00.567'); 160 select a from t1 group by a having COALESCE(a)<"2002-01-01"; 161 drop table t1; 162 163 164 #ON CONDITION 165 drop table if exists t1; 166 drop table if exists t2; 167 create table t1(a INT, b varchar(255)); 168 create table t2(a INT, b varchar(255)); 169 insert into t1 values(1, "你好"), (3, "再见"); 170 insert into t2 values(2, "日期时间"), (4, "明天"); 171 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (length(COALESCE(t1.b)) = length(COALESCE(t2.b))); 172 drop table t1; 173 drop table t2; 174 175 #json type 176 SELECT COALESCE(NULL, NULL, NULL, CAST('{"_id":"192312412512"}' AS JSON), NULL, CAST('{"_id":"192312412513"}' AS JSON)); 177 178 create table t1(a INT, b json); 179 create table t2(a INT, b json); 180 insert into t1 values(1, CAST('{"_id":"192312412512"}' AS JSON)), (3, CAST('{"_id":"192312412513"}' AS JSON)); 181 insert into t2 values(2, CAST('{"_id":"192312412514"}' AS JSON)), (4, CAST('{"_id":"192312412515"}' AS JSON)); 182 select * from t1 union all select * from t2; 183 drop table t1; 184 drop table t2; 185 186 #blob type 187 SELECT COALESCE(NULL, NULL, NULL, CAST('test' AS BLOB), NULL, CAST('1234589002' AS BLOB)); 188 189 DROP table if exists t1; 190 DROP table if exists t2; 191 CREATE TABLE t1 (a INT, s BLOB); 192 INSERT INTO t1 VALUES (1, 'test'); 193 CREATE TABLE t2 (b INT, s BLOB); 194 INSERT INTO t2 VALUES (2, '1234589002'); 195 select * from t1 union all select * from t2; 196 drop table t1; 197 drop table t2; 198 199 #text type 200 SELECT COALESCE(NULL, NULL, NULL, CAST('abcdef' AS text), NULL, CAST('1234589002' AS text)); 201 202 DROP table if exists t1; 203 DROP table if exists t2; 204 CREATE TABLE t1 (a INT, s text); 205 INSERT INTO t1 VALUES (1, 'abcdef'); 206 CREATE TABLE t2 (b INT, s text); 207 INSERT INTO t2 VALUES (2, 'abcdefgh'); 208 select * from t1 union all select * from t2; 209 drop table t1; 210 drop table t2;