github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_if.result (about) 1 set time_zone="+00:00"; 2 SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2); 3 IF(NULL AND 1, 1, 2) IF(1 AND NULL, 1, 2) 4 2 2 5 create table t1 (a int); 6 insert into t1 values (0),(1),(NULL); 7 SELECT * FROM t1 WHERE IF(a AND 1, 0, 1); 8 a 9 0 10 null 11 SELECT * FROM t1 WHERE IF(1 AND a, 0, 1); 12 a 13 0 14 null 15 drop table t1; 16 drop table if exists t; 17 CREATE TABLE t (i1 INT, 18 d1 DOUBLE, 19 e2 DECIMAL(5,2)); 20 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), 21 ( null, 9.0, 10.0/3), 22 ( 1, null, 10.0/3), 23 ( 2, 2.0, null ); 24 SELECT coalesce(e2,i1) nullif_c, IF(e2 IS NULL,i1,e2) if_c, SUM(d1) FROM t GROUP BY e2,i1 ORDER BY nullif_c, SUM(d1); 25 nullif_c if_c SUM(d1) 26 2.00 2.00 2.0 27 3.33 3.33 null 28 3.33 3.33 6.0 29 3.33 3.33 9.0 30 DROP TABLE t; 31 CREATE TABLE source(bt INTEGER, bf INTEGER, i8u BIGINT UNSIGNED, i8s BIGINT); 32 INSERT INTO source VALUES 33 (1,0,0,-9223372036854775808), (1,0,18446744073709551615,9223372036854775807); 34 SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s 35 FROM source; 36 Data truncation: data out of range: data type int64, value '18446744073709551615' 37 DROP TABLE source; 38 create table t1 (num double(12,2)); 39 insert into t1 values (144.54); 40 select sum(if(num is null,0.00,num)) from t1; 41 sum(if(num is null,0.00,num)) 42 144.54 43 drop table t1; 44 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; 45 i c co 46 1111111111111111111 1111111111111111111 1111111111111111111 47 create table t1 (a bigint unsigned); 48 insert into t1 select (if(1, 9223372036854775808, 1)); 49 Data truncation: data out of range: data type int64, value '9223372036854775808' 50 insert into t1 select (case when 1 then 9223372036854775808 else 1 end); 51 Data truncation: data out of range: data type int64, value '9223372036854775808' 52 insert into t1 select (coalesce(9223372036854775808, 1)); 53 Data truncation: data out of range: data type int64, value '9223372036854775808' 54 select * from t1; 55 a 56 drop table t1; 57 drop table if exists t; 58 CREATE TABLE t (i1 INT, 59 d1 DOUBLE, 60 e2 DECIMAL(5,2)); 61 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), 62 ( null, 9.0, 10.0/3), 63 ( 1, null, 10.0/3), 64 ( 2, 2.0, null ); 65 SELECT IF(e2 IS NULL,i1,e2) if_c, 66 SUM(d1) FROM t 67 GROUP BY e2,i1 ORDER BY SUM(d1); 68 if_c SUM(d1) 69 3.33 null 70 2.00 2.0 71 3.33 6.0 72 3.33 9.0 73 DROP TABLE t; 74 create table t1 (f1 int, f2 int); 75 insert into t1 values (0,1),(1,2); 76 select count(distinct if(f1,3,f2)) from t1; 77 count(distinct if(f1,3,f2)) 78 2 79 drop table t1; 80 CREATE TABLE `t2` ( 81 `CONCAT_WS(' ',a)` varchar(9) DEFAULT NULL, 82 `IF(a,a,'')` varchar(9) DEFAULT NULL, 83 `CASE WHEN a THEN a ELSE '' END` varchar(9) DEFAULT NULL, 84 `COALESCE(a,'')` varchar(9) NOT NULL DEFAULT '' 85 ); 86 DROP TABLE t2; 87 CREATE TABLE t1 (a INT); 88 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 89 a 90 INSERT INTO t1 VALUES(1); 91 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 92 a 93 1 94 INSERT INTO t1 VALUES(2),(3); 95 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 96 a 97 1 98 2 99 DROP TABLE t1; 100 select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 ; 101 invalid argument function if, bad value [ANY VARCHAR VARCHAR] 102 CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL); 103 INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); 104 select if(1,st,st) s from t1 order by s; 105 s 106 a 107 A 108 a 109 aa 110 AA 111 aaa 112 BBB 113 select if(u=1,st,st) s from t1 order by s; 114 s 115 a 116 A 117 a 118 aa 119 AA 120 aaa 121 BBB 122 select if(u=1,st,st) s from t1 where st like "%a%" order by s; 123 s 124 a 125 A 126 a 127 aa 128 AA 129 aaa 130 drop table t1; 131 create table t1 (num double(12,2)); 132 insert into t1 values (144.54); 133 select sum(if(num is null,0.00,num)) from t1; 134 sum(if(num is null,0.00,num)) 135 144.54 136 drop table t1; 137 create table t1 (x int, y int); 138 insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); 139 select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; 140 min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) 141 6 56 142 drop table t1; 143 create table t1 (a int); 144 insert t1 values (1),(2); 145 select if(1>2,a,avg(a)) from t1; 146 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 147 drop table t1; 148 CREATE TABLE `t1` ( 149 `id` int(11) NOT NULL , 150 `date` int(10) default NULL, 151 `text` varchar(32) NOT NULL 152 ); 153 INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); 154 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; 155 id date_ord text 156 1 05-03-2005 Day 1 157 2 16-03-2005 Day 2 158 3 28-03-2005 Day 3 159 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; 160 id date_ord text 161 3 28-03-2005 Day 3 162 2 16-03-2005 Day 2 163 1 05-03-2005 Day 1 164 DROP TABLE t1; 165 create table t1 (f1 int, f2 int); 166 insert into t1 values(1,1),(0,0); 167 select f1, any_value(f2), if(f1, 40.0, 5.00) from t1 group by f1; 168 f1 any_value(f2) if(f1, 40.0, 5.00) 169 1 1 40.00 170 0 0 5.00 171 drop table t1; 172 select if(0, 18446744073709551610, 18446744073709551610); 173 if(0, 18446744073709551610, 18446744073709551610) 174 18446744073709551610 175 CREATE TABLE t1(a DECIMAL(10,3)); 176 SELECT t1.a, 177 IF((ROUND(t1.a,2)=1), 2, 178 IF((ROUND(t1.a,2)=1), 2, 179 IF((ROUND(t1.a,2)=1), 2, 180 IF((ROUND(t1.a,2)=1), 2,0)))) + 1 FROM t1; 181 a IF((ROUND(t1.a,2)=1), 2, 182 IF((ROUND(t1.a,2)=1), 2, 183 IF((ROUND(t1.a,2)=1), 2, 184 IF((ROUND(t1.a,2)=1), 2,0)))) + 1 185 DROP TABLE t1; 186 CREATE TABLE t1 (c varchar(255)); 187 INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); 188 SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; 189 Data truncation: data out of range: data type int64, value '12345678901234567890' 190 DROP TABLE t1; 191 CREATE TABLE t1 (a int, b DOUBLE); 192 INSERT INTO t1 VALUES (NULL, 0), (NULL, 1); 193 SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c; 194 c 195 0.0 196 null 197 DROP TABLE t1; 198 SELECT if(0, (SELECT min('hello')), NULL); 199 if(0, (SELECT min('hello')), NULL) 200 null 201 SELECT if(1, (SELECT min('hello')), NULL); 202 if(1, (SELECT min('hello')), NULL) 203 hello 204 SELECT if(0, NULL, (SELECT min('hello'))); 205 if(0, NULL, (SELECT min('hello'))) 206 hello 207 SELECT if(1, NULL, (SELECT min('hello'))); 208 if(1, NULL, (SELECT min('hello'))) 209 null 210 CREATE TABLE t1(c1 INT); 211 INSERT INTO t1 VALUES(1); 212 UPDATE t1 SET c1 = 2 WHERE IF(true, '2015-01-01', '2015-01-01') IS NOT NULL; 213 DROP TABLE t1; 214 CREATE TABLE test_grids_1 ( 215 unq_id int(11) NOT NULL DEFAULT 0, 216 var_fld int(11) DEFAULT 0 217 ); 218 INSERT INTO test_grids_1 VALUES 219 (1,4500), 220 (2,6000); 221 CREATE TABLE test_grid_dtl_1 ( 222 dtl_id int(11) NOT NULL DEFAULT 0, 223 unq_id int(11) DEFAULT 0 224 ); 225 INSERT INTO test_grid_dtl_1 VALUES 226 (1,1), 227 (2,1), 228 (3,2); 229 SELECT g.calc_var, g.if_var, g.case_var 230 FROM 231 ( 232 SELECT unq_id, 233 IF ( var_fld > 5000, ( 1 / var_fld ) , 5000 ) calc_var, 234 IF ( var_fld > 5000, ( 00001 / var_fld ) , 5000 ) if_var, 235 CASE var_fld > 5000 WHEN TRUE THEN ( 1 / var_fld ) ELSE 5000 END case_var 236 FROM 237 test_grids_1 238 ) g 239 JOIN 240 test_grid_dtl_1 d USING (unq_id) 241 ORDER BY 1 242 ; 243 calc_var if_var case_var 244 0.0002 0.0002 0.0002 245 5000.0000 5000.0000 5000.0000 246 5000.0000 5000.0000 5000.0000 247 DROP TABLE test_grids_1; 248 drop table test_grid_dtl_1;