github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_if.test (about) 1 2 set time_zone="+00:00"; 3 SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2); 4 5 6 create table t1 (a int); 7 insert into t1 values (0),(1),(NULL); 8 SELECT * FROM t1 WHERE IF(a AND 1, 0, 1); 9 SELECT * FROM t1 WHERE IF(1 AND a, 0, 1); 10 drop table t1; 11 12 drop table if exists t; 13 CREATE TABLE t (i1 INT, 14 d1 DOUBLE, 15 e2 DECIMAL(5,2)); 16 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), 17 ( null, 9.0, 10.0/3), 18 ( 1, null, 10.0/3), 19 ( 2, 2.0, null ); 20 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); 21 DROP TABLE t; 22 23 CREATE TABLE source(bt INTEGER, bf INTEGER, i8u BIGINT UNSIGNED, i8s BIGINT); 24 INSERT INTO source VALUES 25 (1,0,0,-9223372036854775808), (1,0,18446744073709551615,9223372036854775807); 26 SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s 27 FROM source; 28 DROP TABLE source; 29 30 create table t1 (num double(12,2)); 31 insert into t1 values (144.54); 32 select sum(if(num is null,0.00,num)) from t1; 33 drop table t1; 34 35 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; 36 37 create table t1 (a bigint unsigned); 38 insert into t1 select (if(1, 9223372036854775808, 1)); 39 insert into t1 select (case when 1 then 9223372036854775808 else 1 end); 40 insert into t1 select (coalesce(9223372036854775808, 1)); 41 select * from t1; 42 drop table t1; 43 44 drop table if exists t; 45 CREATE TABLE t (i1 INT, 46 d1 DOUBLE, 47 e2 DECIMAL(5,2)); 48 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), 49 ( null, 9.0, 10.0/3), 50 ( 1, null, 10.0/3), 51 ( 2, 2.0, null ); 52 SELECT IF(e2 IS NULL,i1,e2) if_c, 53 SUM(d1) FROM t 54 GROUP BY e2,i1 ORDER BY SUM(d1); 55 DROP TABLE t; 56 57 58 59 create table t1 (f1 int, f2 int); 60 insert into t1 values (0,1),(1,2); 61 select count(distinct if(f1,3,f2)) from t1; 62 drop table t1; 63 64 65 CREATE TABLE `t2` ( 66 `CONCAT_WS(' ',a)` varchar(9) DEFAULT NULL, 67 `IF(a,a,'')` varchar(9) DEFAULT NULL, 68 `CASE WHEN a THEN a ELSE '' END` varchar(9) DEFAULT NULL, 69 `COALESCE(a,'')` varchar(9) NOT NULL DEFAULT '' 70 ); 71 DROP TABLE t2; 72 73 CREATE TABLE t1 (a INT); 74 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 75 INSERT INTO t1 VALUES(1); 76 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 77 INSERT INTO t1 VALUES(2),(3); 78 SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/1))); 79 DROP TABLE t1; 80 81 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 ; 82 83 84 85 -- @bvt:issue#3334 86 CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL); 87 INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); 88 select if(1,st,st) s from t1 order by s; 89 select if(u=1,st,st) s from t1 order by s; 90 select if(u=1,st,st) s from t1 where st like "%a%" order by s; 91 drop table t1; 92 -- @bvt:issue 93 94 create table t1 (num double(12,2)); 95 insert into t1 values (144.54); 96 select sum(if(num is null,0.00,num)) from t1; 97 drop table t1; 98 99 100 create table t1 (x int, y int); 101 insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); 102 select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; 103 drop table t1; 104 105 106 107 create table t1 (a int); 108 insert t1 values (1),(2); 109 select if(1>2,a,avg(a)) from t1; 110 drop table t1; 111 112 CREATE TABLE `t1` ( 113 `id` int(11) NOT NULL , 114 `date` int(10) default NULL, 115 `text` varchar(32) NOT NULL 116 ); 117 INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); 118 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; 119 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; 120 DROP TABLE t1; 121 122 123 create table t1 (f1 int, f2 int); 124 insert into t1 values(1,1),(0,0); 125 select f1, any_value(f2), if(f1, 40.0, 5.00) from t1 group by f1; 126 drop table t1; 127 128 select if(0, 18446744073709551610, 18446744073709551610); 129 130 CREATE TABLE t1(a DECIMAL(10,3)); 131 -- @bvt:issue#2302 132 SELECT t1.a, 133 IF((ROUND(t1.a,2)=1), 2, 134 IF((ROUND(t1.a,2)=1), 2, 135 IF((ROUND(t1.a,2)=1), 2, 136 IF((ROUND(t1.a,2)=1), 2,0)))) + 1 FROM t1; 137 -- @bvt:issue 138 DROP TABLE t1; 139 140 141 CREATE TABLE t1 (c varchar(255)); 142 INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); 143 SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; 144 DROP TABLE t1; 145 146 147 -- @bvt:issue#3265 148 CREATE TABLE t1 (a int, b DOUBLE); 149 INSERT INTO t1 VALUES (NULL, 0), (NULL, 1); 150 SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c; 151 DROP TABLE t1; 152 -- @bvt:issue 153 154 SELECT if(0, (SELECT min('hello')), NULL); 155 SELECT if(1, (SELECT min('hello')), NULL); 156 SELECT if(0, NULL, (SELECT min('hello'))); 157 SELECT if(1, NULL, (SELECT min('hello'))); 158 159 CREATE TABLE t1(c1 INT); 160 INSERT INTO t1 VALUES(1); 161 UPDATE t1 SET c1 = 2 WHERE IF(true, '2015-01-01', '2015-01-01') IS NOT NULL; 162 DROP TABLE t1; 163 164 165 166 167 CREATE TABLE test_grids_1 ( 168 unq_id int(11) NOT NULL DEFAULT 0, 169 var_fld int(11) DEFAULT 0 170 ); 171 INSERT INTO test_grids_1 VALUES 172 (1,4500), 173 (2,6000); 174 CREATE TABLE test_grid_dtl_1 ( 175 dtl_id int(11) NOT NULL DEFAULT 0, 176 unq_id int(11) DEFAULT 0 177 ); 178 INSERT INTO test_grid_dtl_1 VALUES 179 (1,1), 180 (2,1), 181 (3,2); 182 SELECT g.calc_var, g.if_var, g.case_var 183 FROM 184 ( 185 SELECT unq_id, 186 IF ( var_fld > 5000, ( 1 / var_fld ) , 5000 ) calc_var, 187 IF ( var_fld > 5000, ( 00001 / var_fld ) , 5000 ) if_var, 188 CASE var_fld > 5000 WHEN TRUE THEN ( 1 / var_fld ) ELSE 5000 END case_var 189 FROM 190 test_grids_1 191 ) g 192 JOIN 193 test_grid_dtl_1 d USING (unq_id) 194 ORDER BY 1 195 ; 196 DROP TABLE test_grids_1; 197 drop table test_grid_dtl_1; 198