github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/expression/case_when.result (about) 1 select CASE "b" when "a" then 1 when "b" then 2 END; 2 case b when a then 1 when b then 2 end 3 2 4 select CASE "c" when "a" then 1 when "b" then 2 END; 5 case c when a then 1 when b then 2 end 6 null 7 select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END; 8 case c when a then 1 when b then 2 else 3 end 9 3 10 select CASE when 1=0 then "true" else "false" END; 11 CASE when 1=0 then "true" else "false" END 12 false 13 select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END; 14 case 1 when 1 then one when 2 then two else more end 15 one 16 select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END; 17 case 2.0 when 1 then one when 2.0 then two else more end 18 two 19 select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0; 20 (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 21 2 22 select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0; 23 (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0 24 2.00 25 select case 1/0 when "a" then "true" else "false" END; 26 Data truncation: division by zero 27 select case 1/0 when "a" then "true" END; 28 Data truncation: division by zero 29 select (case 1/0 when "a" then "true" END) | 0; 30 Data truncation: division by zero 31 select (case 1/0 when "a" then "true" END) + 0.0; 32 Data truncation: division by zero 33 select case when 1>0 then "TRUE" else "FALSE" END; 34 case when 1>0 then "TRUE" else "FALSE" END 35 TRUE 36 select case when 1<0 then "TRUE" else "FALSE" END; 37 case when 1<0 then "TRUE" else "FALSE" END 38 FALSE 39 SELECT CAST(CASE WHEN 0 THEN '2001-01-01' END AS DATE); 40 cast(case when 0 then 2001-01-01 end as date) 41 null 42 SELECT CAST(CASE WHEN 0 THEN DATE'2001-01-01' END AS DATE); 43 cast(case when 0 then 2001-01-01 end as date) 44 null 45 select case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END; 46 case 1.0 when 0.1 then a when 1.0 then b else c end 47 b 48 select case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END; 49 case 0.1 when 0.1 then a when 1.0 then b else c end 50 a 51 select case 1 when 0.1 then "a" when 1.0 then "b" else "c" END; 52 case 1 when 0.1 then a when 1.0 then b else c end 53 b 54 select case 1.0 when 0.1 then "a" when 1 then "b" else "c" END; 55 case 1.0 when 0.1 then a when 1 then b else c end 56 b 57 select case 1.001 when 0.1 then "a" when 1 then "b" else "c" END; 58 case 1.001 when 0.1 then a when 1 then b else c end 59 c 60 drop table if exists t1; 61 drop table if exists t2; 62 CREATE TABLE t1 (a varchar(10), PRIMARY KEY (a)); 63 CREATE TABLE t2 (a varchar(10), b date, PRIMARY KEY(a)); 64 INSERT INTO t1 VALUES ('test1'); 65 INSERT INTO t2 VALUES 66 ('test1','2016-12-13'),('test2','2016-12-14'),('test3','2016-12-15'); 67 SELECT b, b = '20161213', 68 CASE b WHEN '20161213' then 'found' ELSE 'not found' END FROM t2; 69 b b = '20161213' CASE b WHEN '20161213' then 'found' ELSE 'not found' END 70 2016-12-13 1 found 71 2016-12-14 0 not found 72 2016-12-15 0 not found 73 drop table if exists t1; 74 create table t1 (a int); 75 insert into t1 values(1),(2),(3),(4); 76 select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; 77 fcase count(*) 78 2 1 79 3 1 80 0 2 81 select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; 82 fcase count(*) 83 one 1 84 two 1 85 nothing 2 86 drop table if exists t1; 87 create table t1 (`row` int not null, col int not null, val varchar(255) not null); 88 insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small'); 89 select max(case col when 1 then val else null end) as color from t1 group by `row`; 90 color 91 orange 92 yellow 93 green 94 drop table if exists t1; 95 create table t1(a float, b int default 3); 96 insert into t1 (a) values (2), (11), (8); 97 select min(a), min(case when 1=1 then a else NULL end), 98 min(case when 1!=1 then NULL else a end) 99 from t1 where b=3 group by b; 100 min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end) 101 2.0 2.0 2.0 102 drop table if exists t1; 103 CREATE TABLE t1 (a INT, b INT); 104 INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); 105 SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 GROUP BY b; 106 CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END 107 Positive 108 Positive 109 Positive 110 drop table if exists t1; 111 drop table if exists t1; 112 drop table if exists t2; 113 create table t1 (a int, b bigint unsigned); 114 create table t2 (c int); 115 insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), 116 (3,11120436154190595086); 117 insert into t2 (c) values (1), (2), (3); 118 select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 119 join t2 on t1.a=t2.c order by d; 120 Data truncation: data out of range: data type int64, value '18196094287899841997' 121 select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 122 join t2 on t1.a=t2.c where b=11120436154190595086 order by d; 123 Data truncation: data out of range: data type int64, value '11120436154190595086' 124 drop table if exists small; 125 drop table if exists big; 126 CREATE TABLE small (id int not null,PRIMARY KEY (id)); 127 CREATE TABLE big (id int not null,PRIMARY KEY (id)); 128 INSERT INTO small VALUES (1), (2); 129 INSERT INTO big VALUES (1), (2), (3), (4); 130 SELECT big.*, dt.* FROM big LEFT JOIN (SELECT id as dt_id, 131 CASE id WHEN 0 THEN 0 ELSE 1 END AS simple, 132 CASE WHEN id=0 THEN NULL ELSE 1 END AS cond 133 FROM small) AS dt 134 ON big.id=dt.dt_id; 135 id dt_id simple cond 136 1 1 1 1 137 2 2 1 1 138 3 null null null 139 4 null null null 140 drop table if exists small; 141 drop table if exists big; 142 SELECT 'case+union+test' 143 UNION 144 SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END; 145 case+union+test 146 case+union+test 147 nobug 148 drop table t1; 149 CREATE TABLE t1(a int); 150 insert into t1 values(1),(1),(2),(1),(3),(2),(1); 151 SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; 152 1 153 1 154 1 155 1 156 1 157 DROP TABLE if exists t1; 158 DROP TABLE if exists t1; 159 create table t1 (USR_ID int not null, MAX_REQ int not null); 160 insert into t1 values (1, 3); 161 select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; 162 count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ 163 1 164 select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; 165 Case When Count(*) < MAX_REQ Then 1 Else 0 End 166 1 167 DROP TABLE if exists t1; 168 select case when 1 in (1.0, 2.0, 3.0) then true else false end; 169 case when 1 in (1.0, 2.0, 3.0) then true else false end 170 true 171 DROP TABLE if exists t1; 172 CREATE TABLE t1 ( 173 id int NOT NULL AUTO_INCREMENT, 174 key_num int NOT NULL DEFAULT '0', 175 hiredate date NOT NULL, 176 PRIMARY KEY (id), 177 KEY key_num (key_num) 178 ); 179 insert into t1 values 180 (1, 7369, '1980-12-17'), 181 (2, 7499, '1981-02-20'), 182 (3, 7521, '1981-02-22'), 183 (4, 7566, '1981-04-02'), 184 (5, 7654, '1981-09-28'), 185 (6, 7698, '1981-05-01'), 186 (7, 7782, '1981-06-09'), 187 (8, 7788, '0087-07-13'), 188 (9, 7839, '1981-11-17'), 189 (10, 7844, '1981-09-08'), 190 (11, 7876, '2007-07-13'), 191 (12, 7900, '1981-12-03'), 192 (13, 7980, '1987-07-13'), 193 (14, 7981, '2001-11-17'), 194 (15, 7982, '1951-11-08'), 195 (16, 7983, '1927-10-13'), 196 (17, 7984, '1671-12-09'), 197 (18, 7985, '1981-11-06'), 198 (19, 7986, '1771-12-06'), 199 (20, 7987, '1985-10-06'); 200 select id, case when id < 5 then 0 when id < 10 then 1 when id < 15 then 2 when true then 3 else -1 end as xxx from t1; 201 id xxx 202 1 0 203 2 0 204 3 0 205 4 0 206 5 1 207 6 1 208 7 1 209 8 1 210 9 1 211 10 2 212 11 2 213 12 2 214 13 2 215 14 2 216 15 3 217 16 3 218 17 3 219 18 3 220 19 3 221 20 3 222 DROP TABLE t1;