github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_coalesce_1.sql (about)

     1  
     2  -- test coalesce function
     3  
     4  drop table if exists t1;
     5  create table t1(
     6  a int,
     7  b varchar(25)
     8  );
     9  
    10  insert into  t1 values (0, 'a');
    11  insert into  t1 values (1, NULL);
    12  insert into  t1 values (NULL, NULL);
    13  insert into  t1 values (null, 'b');
    14  
    15  select * from t1;
    16  select coalesce(a, 1) from t1;
    17  -- echo error
    18  select coalesce(b, 1) from t1;
    19  
    20  select coalesce(b, '1') from t1;
    21  
    22  drop table t1;
    23  
    24  
    25  drop table if exists t2;
    26  create table t2(
    27  a float,
    28  b datetime
    29  );
    30  
    31  insert into t2 values (12.345, '2022-02-20 10:10:10.999999');
    32  insert into t2 values (3.45646, NULL);
    33  insert into t2 values(NULL, '2023-04-03 22:10:29.999999');
    34  insert into t2 values (NULL, NULL);
    35  
    36  select * from t2;
    37  select coalesce(a, 1.0) from t2;
    38  select coalesce(a, 1) from t2;
    39  
    40  select coalesce(b, 2022-01-01) from t2;
    41  select coalesce(b, 2022) from t2;
    42  select coalesce(b, 2) from t2;
    43  
    44  
    45  select coalesce(b, '2022-10-01') from t2;
    46  select coalesce(b, '2022-10-01 10:10:10.999999') from t2;
    47  
    48  -- echo error
    49  select coalesce(b, '2022') from t2;
    50  
    51  -- echo error
    52  select coalesce(b, '2022/10/01') from t2;
    53  drop table t2;
    54  
    55  
    56  drop table if exists t3;
    57  create table t3(
    58  a bool,
    59  b text
    60  );
    61  
    62  insert into t3 values (0, 'a');
    63  insert into t3 values (1, 'b');
    64  insert into t3 values (NULL, 'c');
    65  insert into t3 values (TRUE, NULL);
    66  insert into t3 values (NULL, NULL);
    67  
    68  select * from t3;
    69  
    70  select coalesce(a, 1) from t3;
    71  select coalesce(a, 0) from t3;
    72  -- echo error
    73  select coalesce(a, 200) from t3;
    74  
    75  select coalesce(b, '1') from t3;
    76  select coalesce(b, 'bull') from t3;
    77  
    78  drop table t3;
    79  
    80  
    81  -- test create view
    82  drop table if exists t4;
    83  create table t4 (f1 date, f2 datetime, f3 varchar(20));
    84  
    85  drop view if exists view_t1;
    86  create view view_t1 as select coalesce(f1,f1) as f4 from t4;
    87  desc view_t1;
    88  drop view view_t1;
    89  
    90  drop view if exists view_t2;
    91  create view view_t2 as select coalesce(f1,f2) as f4 from t4;
    92  desc view_t2;
    93  drop view view_t2;
    94  
    95  
    96  drop view if exists view_t3;
    97  create view view_t3 as select coalesce(f2,f2) as f4 from t4;
    98  desc view_t3;
    99  drop view view_t3;
   100  
   101  drop view if exists view_t4;
   102  create view view_t4 as select coalesce(f1,f3) as f4 from t4;
   103  desc view_t4;
   104  drop view view_t4;
   105  
   106  drop view if exists view_t5;
   107  create view view_t5 as select coalesce(f2,f3) as f4 from t4;
   108  desc view_t5;
   109  drop view view_t5;
   110  
   111  drop table t4;
   112  
   113  
   114  
   115  drop table if exists t5;
   116  CREATE TABLE t5 (b datetime);
   117  
   118  INSERT INTO t5 VALUES ('2010-01-01 00:00:00'), ('2010-01-01 00:00:00');
   119  SELECT * FROM t5 WHERE b <= coalesce(NULL, now());
   120  
   121  DROP TABLE t5;