github.com/matrixorigin/matrixone@v0.7.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 invalid input: column fcase does not exist 78 select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase; 79 invalid input: column fcase does not exist 80 select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase; 81 invalid input: column fcase does not exist 82 drop table if exists t1; 83 create table t1 (`row` int not null, col int not null, val varchar(255) not null); 84 insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small'); 85 select max(case col when 1 then val else null end) as color from t1 group by `row`; 86 color 87 orange 88 yellow 89 green 90 drop table if exists t1; 91 create table t1(a float, b int default 3); 92 insert into t1 (a) values (2), (11), (8); 93 select min(a), min(case when 1=1 then a else NULL end), 94 min(case when 1!=1 then NULL else a end) 95 from t1 where b=3 group by b; 96 min(a) min(case when 1=1 then a else NULL end) min(case when 1!=1 then NULL else a end) 97 2.0 2.0 2.0 98 drop table if exists t1; 99 CREATE TABLE t1 (a INT, b INT); 100 INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); 101 SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 GROUP BY b; 102 CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END 103 Positive 104 Positive 105 Positive 106 drop table if exists t1; 107 drop table if exists t1; 108 drop table if exists t2; 109 create table t1 (a int, b bigint unsigned); 110 create table t2 (c int); 111 insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), 112 (3,11120436154190595086); 113 insert into t2 (c) values (1), (2), (3); 114 select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 115 join t2 on t1.a=t2.c order by d; 116 Data truncation: data out of range: data type int64, 117 select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 118 join t2 on t1.a=t2.c where b=11120436154190595086 order by d; 119 Data truncation: data out of range: data type int64, 120 drop table if exists small; 121 drop table if exists big; 122 CREATE TABLE small (id int not null,PRIMARY KEY (id)); 123 CREATE TABLE big (id int not null,PRIMARY KEY (id)); 124 INSERT INTO small VALUES (1), (2); 125 INSERT INTO big VALUES (1), (2), (3), (4); 126 SELECT big.*, dt.* FROM big LEFT JOIN (SELECT id as dt_id, 127 CASE id WHEN 0 THEN 0 ELSE 1 END AS simple, 128 CASE WHEN id=0 THEN NULL ELSE 1 END AS cond 129 FROM small) AS dt 130 ON big.id=dt.dt_id; 131 id dt_id simple cond 132 1 1 1 1 133 2 2 1 1 134 3 null null null 135 4 null null null 136 drop table if exists small; 137 drop table if exists big; 138 SELECT 'case+union+test' 139 UNION 140 SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END; 141 case+union+test 142 case+union+test 143 nobug 144 drop table t1; 145 CREATE TABLE t1(a int); 146 insert into t1 values(1),(1),(2),(1),(3),(2),(1); 147 SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END; 148 1 149 1 150 1 151 1 152 1 153 DROP TABLE if exists t1; 154 DROP TABLE if exists t1; 155 create table t1 (USR_ID int not null, MAX_REQ int not null); 156 insert into t1 values (1, 3); 157 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; 158 count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ 159 1 160 select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; 161 Case When Count(*) < MAX_REQ Then 1 Else 0 End 162 1 163 DROP TABLE if exists t1;