github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/conditional (about)

     1  query II
     2  SELECT IF(1 = 2, NULL, 1), IF(2 = 2, NULL, 2)
     3  ----
     4  1  NULL
     5  
     6  query III
     7  SELECT NULLIF(1, 2), NULLIF(2, 2), NULLIF(NULL, NULL)
     8  ----
     9  1  NULL  NULL
    10  
    11  query IIII
    12  SELECT
    13      IFNULL(1, 2),
    14      IFNULL(NULL, 2),
    15      COALESCE(1, 2),
    16      COALESCE(NULL, 2)
    17  ----
    18  1  2  1  2
    19  
    20  statement ok
    21  CREATE TABLE t (a) AS VALUES (1), (2), (3)
    22  
    23  query IT
    24  SELECT
    25      a,
    26      CASE
    27      WHEN a = 1 THEN 'one'
    28      WHEN a = 2 THEN 'two'
    29      ELSE 'other'
    30      END
    31  FROM
    32      t
    33  ORDER BY
    34      a
    35  ----
    36  1  one
    37  2  two
    38  3  other
    39  
    40  query IT
    41  SELECT
    42      a,
    43      CASE a
    44      WHEN 1 THEN 'one'
    45      WHEN 2 THEN 'two'
    46      ELSE 'other'
    47      END
    48  FROM
    49      t
    50  ORDER BY
    51      a
    52  ----
    53  1  one
    54  2  two
    55  3  other
    56  
    57  query III
    58  SELECT a, NULLIF(a, 2), IF(a = 2, NULL, a) FROM t ORDER BY a
    59  ----
    60  1  1     1
    61  2  NULL  NULL
    62  3  3     3
    63  
    64  query TTTT
    65  SELECT
    66      CASE
    67      WHEN false THEN 'one'
    68      WHEN true THEN 'two'
    69      ELSE 'three'
    70      END,
    71      CASE 1
    72      WHEN 2 THEN 'two'
    73      WHEN 1 THEN 'one'
    74      ELSE 'three'
    75      END,
    76      CASE
    77      WHEN false THEN 'one'
    78      ELSE 'three'
    79      END,
    80      CASE
    81      WHEN false THEN 'one'
    82      END
    83  ----
    84  two  one  three  NULL
    85  
    86  query TTTTT
    87  SELECT
    88      CASE
    89      WHEN 1 = 1 THEN 'one'
    90      END,
    91      CASE false
    92      WHEN 0 = 1 THEN 'one'
    93      END,
    94      CASE 1
    95      WHEN 2 THEN 'one'
    96      ELSE 'three'
    97      END,
    98      CASE NULL
    99      WHEN true THEN 'one'
   100      WHEN false THEN 'two'
   101      WHEN NULL THEN 'three'
   102      ELSE 'four'
   103      END,
   104      CASE
   105      WHEN false THEN 'one'
   106      WHEN true THEN 'two'
   107      END
   108  ----
   109  one  one  three  four  two