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

     1  drop table if exists t1;
     2  create table t1(
     3  a int,
     4  b varchar(25)
     5  );
     6  insert into  t1 values (0, 'a');
     7  insert into  t1 values (1, NULL);
     8  insert into  t1 values (NULL, NULL);
     9  insert into  t1 values (null, 'b');
    10  select * from t1;
    11  a    b
    12  0    a
    13  1    null
    14  null    null
    15  null    b
    16  select coalesce(a, 1) from t1;
    17  coalesce(a, 1)
    18  0
    19  1
    20  1
    21  1
    22  select coalesce(b, 1) from t1;
    23  invalid argument cast to int, bad value a
    24  select coalesce(b, '1') from t1;
    25  coalesce(b, 1)
    26  a
    27  1
    28  1
    29  b
    30  drop table t1;
    31  drop table if exists t2;
    32  create table t2(
    33  a float,
    34  b datetime
    35  );
    36  insert into t2 values (12.345, '2022-02-20 10:10:10.999999');
    37  insert into t2 values (3.45646, NULL);
    38  insert into t2 values(NULL, '2023-04-03 22:10:29.999999');
    39  insert into t2 values (NULL, NULL);
    40  select * from t2;
    41  a    b
    42  12.345    2022-02-20 10:10:11
    43  3.45646    null
    44  null    2023-04-03 22:10:30
    45  null    null
    46  select coalesce(a, 1.0) from t2;
    47  coalesce(a, 1.0)
    48  12.345000267028809
    49  3.4564599990844727
    50  1.0
    51  1.0
    52  select coalesce(a, 1) from t2;
    53  coalesce(a, 1)
    54  12.345000267028809
    55  3.4564599990844727
    56  1.0
    57  1.0
    58  select coalesce(b, 2022-01-01) from t2;
    59  coalesce(b, 2022 - 1 - 1)
    60  2022-02-20 10:10:11
    61  2020
    62  2023-04-03 22:10:30
    63  2020
    64  select coalesce(b, 2022) from t2;
    65  coalesce(b, 2022)
    66  2022-02-20 10:10:11
    67  2022
    68  2023-04-03 22:10:30
    69  2022
    70  select coalesce(b, 2) from t2;
    71  coalesce(b, 2)
    72  2022-02-20 10:10:11
    73  2
    74  2023-04-03 22:10:30
    75  2
    76  select coalesce(b, '2022-10-01') from t2;
    77  coalesce(b, 2022-10-01)
    78  2022-02-20 10:10:11
    79  2022-10-01
    80  2023-04-03 22:10:30
    81  2022-10-01
    82  select coalesce(b, '2022-10-01 10:10:10.999999') from t2;
    83  coalesce(b, 2022-10-01 10:10:10.999999)
    84  2022-02-20 10:10:11
    85  2022-10-01 10:10:10.999999
    86  2023-04-03 22:10:30
    87  2022-10-01 10:10:10.999999
    88  select coalesce(b, '2022') from t2;
    89  coalesce(b, 2022)
    90  2022-02-20 10:10:11
    91  2022
    92  2023-04-03 22:10:30
    93  2022
    94  select coalesce(b, '2022/10/01') from t2;
    95  coalesce(b, 2022/10/01)
    96  2022-02-20 10:10:11
    97  2022/10/01
    98  2023-04-03 22:10:30
    99  2022/10/01
   100  drop table t2;
   101  drop table if exists t3;
   102  create table t3(
   103  a bool,
   104  b text
   105  );
   106  insert into t3 values (0, 'a');
   107  insert into t3 values (1, 'b');
   108  insert into t3 values (NULL, 'c');
   109  insert into t3 values (TRUE, NULL);
   110  insert into t3 values (NULL, NULL);
   111  select * from t3;
   112  a    b
   113  false    a
   114  true    b
   115  null    c
   116  true    null
   117  null    null
   118  select coalesce(a, 1) from t3;
   119  coalesce(a, 1)
   120  false
   121  true
   122  true
   123  true
   124  true
   125  select coalesce(a, 0) from t3;
   126  coalesce(a, 0)
   127  false
   128  true
   129  false
   130  true
   131  false
   132  select coalesce(a, 200) from t3;
   133  coalesce(a, 200)
   134  false
   135  true
   136  true
   137  true
   138  true
   139  select coalesce(b, '1') from t3;
   140  coalesce(b, 1)
   141  a
   142  b
   143  c
   144  1
   145  1
   146  select coalesce(b, 'bull') from t3;
   147  coalesce(b, bull)
   148  a
   149  b
   150  c
   151  bull
   152  bull
   153  drop table t3;
   154  drop table if exists t4;
   155  create table t4 (f1 date, f2 datetime, f3 varchar(20));
   156  drop view if exists view_t1;
   157  create view view_t1 as select coalesce(f1,f1) as f4 from t4;
   158  desc view_t1;
   159  Field    Type    Null    Key    Default    Extra    Comment
   160  f4    DATE(0)    YES        null
   161  drop view view_t1;
   162  drop view if exists view_t2;
   163  create view view_t2 as select coalesce(f1,f2) as f4 from t4;
   164  desc view_t2;
   165  Field    Type    Null    Key    Default    Extra    Comment
   166  f4    DATETIME(0)    YES        null
   167  drop view view_t2;
   168  drop view if exists view_t3;
   169  create view view_t3 as select coalesce(f2,f2) as f4 from t4;
   170  desc view_t3;
   171  Field    Type    Null    Key    Default    Extra    Comment
   172  f4    DATETIME(0)    YES        null
   173  drop view view_t3;
   174  drop view if exists view_t4;
   175  create view view_t4 as select coalesce(f1,f3) as f4 from t4;
   176  desc view_t4;
   177  Field    Type    Null    Key    Default    Extra    Comment
   178  f4    VARCHAR(65535)    YES        NULL
   179  drop view view_t4;
   180  drop view if exists view_t5;
   181  create view view_t5 as select coalesce(f2,f3) as f4 from t4;
   182  desc view_t5;
   183  Field    Type    Null    Key    Default    Extra    Comment
   184  f4    VARCHAR(65535)    YES        NULL
   185  drop view view_t5;
   186  drop table t4;
   187  drop table if exists t5;
   188  CREATE TABLE t5 (b datetime);
   189  INSERT INTO t5 VALUES ('2010-01-01 00:00:00'), ('2010-01-01 00:00:00');
   190  SELECT * FROM t5 WHERE b <= coalesce(NULL, now());
   191  b
   192  2010-01-01 00:00:00
   193  2010-01-01 00:00:00
   194  DROP TABLE t5;