github.com/matrixorigin/matrixone@v0.7.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 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
    56  select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
    57  drop table if exists t1;
    58  
    59  -- @case
    60  -- @desc:test for case_when expression with function
    61  -- @label:bvt
    62  create table t1 (`row` int not null, col int not null, val varchar(255) not null);
    63  insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
    64  select max(case col when 1 then val else null end) as color from t1 group by `row`;
    65  drop table if exists t1;
    66  
    67  create table t1(a float, b int default 3);
    68  insert into t1 (a) values (2), (11), (8);
    69  select min(a), min(case when 1=1 then a else NULL end),
    70    min(case when 1!=1 then NULL else a end)
    71  from t1 where b=3 group by b;
    72  
    73  drop table if exists  t1;
    74  CREATE TABLE t1 (a INT, b INT);
    75  INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
    76  SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 GROUP BY b;
    77  
    78  drop table if exists  t1;
    79  
    80  -- @case
    81  -- @desc:test for case_when expression with join
    82  -- @label:bvt
    83  drop table if exists  t1;
    84  drop table if exists  t2;
    85  create table t1 (a int, b bigint unsigned);
    86  create table t2 (c int);
    87  insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),
    88    (3,11120436154190595086);
    89  insert into t2 (c) values (1), (2), (3);
    90  select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1
    91    join t2 on t1.a=t2.c order by d;
    92  select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1
    93    join t2 on t1.a=t2.c where b=11120436154190595086 order by d;
    94  drop table if exists small;
    95  drop table if exists big;
    96  CREATE TABLE small (id int not null,PRIMARY KEY (id));
    97  CREATE TABLE big (id int not null,PRIMARY KEY (id));
    98  INSERT INTO small VALUES (1), (2);
    99  INSERT INTO big VALUES (1), (2), (3), (4);
   100  SELECT big.*, dt.* FROM big LEFT JOIN (SELECT id as dt_id,
   101                             CASE id WHEN 0 THEN 0 ELSE 1 END AS simple,
   102                             CASE WHEN id=0 THEN NULL ELSE 1 END AS cond
   103                      FROM small) AS dt
   104       ON big.id=dt.dt_id;
   105  
   106  drop table if exists small;
   107  drop table if exists big;
   108  
   109  -- @case
   110  -- @desc:test for case_when expression with union
   111  -- @label:bvt
   112  SELECT 'case+union+test'
   113  UNION
   114  SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
   115  
   116  -- @case
   117  -- @desc:test for case_when expression in where filter
   118  -- @label:bvt
   119  drop table t1;
   120  CREATE TABLE t1(a int);
   121  insert into t1 values(1),(1),(2),(1),(3),(2),(1);
   122  SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END;
   123  DROP TABLE if exists t1;
   124  
   125  -- @case
   126  -- @desc:test for case_when expression with count()
   127  -- @label:bvt
   128  DROP TABLE if exists t1;
   129  create table t1 (USR_ID int not null, MAX_REQ int not null);
   130  insert into t1 values (1, 3);
   131  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;
   132  select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
   133  DROP TABLE if exists t1;