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