The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.

An example:

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details.

There is a “simple” form of CASE expression that is a variant of the general form above:

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause (or a null value) is returned. This is similar to the switch statement in C.

The example above can be written using the simple CASE syntax:

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

In MySQL compatibility mode(See lightdb_dblevel_syntax_compatible_type), the result set of an expression can be any combination of characters, numeric values, and dates. When the result set contains more than two kinds of characters, numeric values and dates, the type of returned value is a string.

SELECT CASE WHEN score >= 60 THEN score ELSE 'not pass' END AS score from students;
 

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

SELECT COALESCE(description, short_description, '(none)') ...

This returns description if it is not null, otherwise short_description if it is not null, otherwise (none).

The arguments must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details).

Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.

NULLIF(value1, value2)

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:

SELECT NULLIF(value, '(none)') ...

In this example, if value is (none), null is returned, otherwise the value of value is returned.

The two arguments must be of comparable types. To be specific, they are compared exactly as if you had written value1 = value2, so there must be a suitable = operator available.

The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument's type. For example, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator, only numeric = numeric.

GREATEST(value [, ...])

LEAST(value [, ...])

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.

When the GUC parameter lightdb_dblevel_syntax_compatible_type is set to oracle, the behavior of the GREATEST and LEAST functions adheres to the Oracle standards. In this mode, if any argument evaluates to NULL, the functions return NULL. Additionally, the first argument is used to determine the return type. If the first argument is numeric, then lightdb determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first argument is not numeric, then each argument after the first is implicitly converted to the data type of the first argument before the comparison.

  NVL(expr1, expr2)
  

When expr1 is NULL, expr2 is returned. When expr1 is not NULL, expr1 is returned. The data types of expr1 and expr2 can be different. we support the following different data types.

Name |      Result data type       |     Argument data types       
------+-----------------------------+----------------------------------
nvl  | bit                         | bit, "any"                         
nvl  | bytea                       | bytea, "any"                       
nvl  | date                        | date, "any"                        
nvl  | numeric                     | double precision, "any"            
nvl  | numeric                     | integer, "any"                     
nvl  | numeric                     | numeric, "any"                     
nvl  | text                        | text, "any"                        
nvl  | timestamp without time zone | timestamp without time zone, "any" 
nvl  | timestamp with time zone    | timestamp with time zone, "any"    
nvl  | time without time zone      | time without time zone, "any"    

The NVL example given above:

lightdb@test=# select nvl(1.1, 'test'::text);
nvl 
-----
1.1
(1 row)

In this example expr1 data type is Numeric and the expr2 is text. So the function nvl(numeric, "any") is called and the returned data type is numeric.

In some special cases, the type returned when expr1 is null depends on the source of the data. When the following example is executed, according to the implicit conversion rules, null is actually of unknown type, so the function called is nvl(text, 'any') so the function returns the text type.

 lightdb@test=# select pg_typeof(nvl(null, 1)), nvl(null, 1);
 pg_typeof | nvl 
-----------+-----
 text      | 1
(1 row)
 

In the following example, because the null field is int type in the database, So the function returns a numric type

create table test_nvl (id int, func varchar(20));
insert into test_nvl values (null, 'nvl');

lightdb@test=# select pg_typeof(nvl(id, 1)), nvl(id, 1)  from test;
 pg_typeof | nvl 
-----------+-----
 numeric   |   1
(1 row)
  

Because the type returned by ltrim is text, So in the following example the function actually called is nvl(text, "any"), so the function returns the text type

lightdb@test=# select pg_typeof(nvl(ltrim(func, 'nvl'), 1)), nvl(ltrim(func, 'nvl'), 1) from test;
 pg_typeof | nvl 
-----------+-----
 text      | 1
(1 row)
  

The above features also apply in the functions nvl2, decode, decode2.

  NVL2(expr,substitute1,substitute2)
  

NVL2 returns a substitute value based on whether the specified value is NULL or not NULL. When expr is NULL, substitute2 is returned. When it is not NULL, substitute1 is returned.

Specify the same data types for substitute1 and substitute2, but the expr can be different. For substitute1 and substitute2 we support the following data types.

  
Name  |      Result data type       |        Argument data types       
------+-----------------------------+------------------------------------
nvl2  | bit                         | bit, "any"                         
nvl2  | bytea                       | bytea, "any"                       
nvl2  | date                        | date, "any"                        
nvl2  | numeric                     | double precision, "any"            
nvl2  | numeric                     | integer, "any"                     
nvl2  | numeric                     | numeric, "any"                     
nvl2  | text                        | text, "any"                        
nvl2  | timestamp without time zone | timestamp without time zone, "any" 
nvl2  | timestamp with time zone    | timestamp with time zone, "any"    
nvl2  | time without time zone      | time without time zone, "any"    

The NVL2 example given above:

lightdb@test=# select nvl2('2022-01-01'::date,'ab'::text, 'cd'::text);
nvl2 
------
ab
(1 row)

In this example, the expr data type is date. substitute1 and substitute2 data type is text. So the function nvlnvl2("any", text, "any") is called and the returned data type is text.

The DECODE function compares expr to each search value one by one. If expr is equal to a search, then LightDB returns the corresponding result. If no match is found, then LightDB returns default. If default is omitted, then LightDB returns null.

DECODE(expr, 
      search, result, 
      [search, result], 
      [search, result]..., 
      [default])

The DECODE function(with more than 2 parameters) can only be used in oracle mode in LightDB. That means the GUC parameter lightdb_dblevel_syntax_compatible_type must be set to oracle before we call this function.

LightDB automatically converts expr and each search value to the data type of the first search value before comparing. LightDB automatically converts the return value to the same data type as the first result. An error will be thrown out if type conversion is not supported. If the first result is null, then LightDB converts the return value to the data type TEXT.

lightdb@oracle=# create table t(a char(10),b varchar2(10));
CREATE TABLE
lightdb@oracle=# insert into t values('a','a');
INSERT 0 1
lightdb@oracle=# select decode(a,b,1,2) from t;
 decode 
--------
      2
(1 row)

lightdb@oracle=# select decode(b,a,1,2) from t;
 decode 
--------
      1
(1 row)

lightdb@oracle=# select decode(trim(a),b,1,2) from t;
 decode 
--------
      1
(1 row)

lightdb@oracle=# select pg_typeof(decode(1,2,'3',4));
 pg_typeof 
-----------
 text
(1 row)

The search, result, and default values can be derived from expressions. LightDB Database uses short-circuit evaluation to result and default. The database evaluates the result expression only if expr and search are equal, otherwise the evaluation step will be skipped. LightDB evaluates the default expression only if expr is not equal to any search expressions. The above short-curcuit rule is based on a precondition, which is there exists a coercion between the source data type and the target data type. If not, an error will be thrown out. But the short-circuit evaluation rule does NOT apply to search expressions which means LightDB will convert the type of all search expressions to that of the first search from the beginning.

lightdb@oracle=# select decode(3,
                               1,1/0, --short-circuit on result, no division by zero error
                               2,2/0, --short-circuit on result, no division by zero error
                               3,300,
                               'x'    --short-circuit on default, no type convertion error
                               );
 decode 
--------
    300
(1 row)

SELECT DECODE(123, 
  123, 'Number Match', 
  'Not Match', 'Default') --'Not Match' cannot convert to int(type of 1st search)
  FROM DUAL;
ERROR:  invalid input syntax for type numeric: "Not Match"
LINE 3:   'Not Match', 'Default') --'Not Match' cannot convert to in...

In a DECODE function, LightDB considers two nulls to be equivalent. If expr is null, then LightDB returns the result of the first search that is also null.

lightdb@oracle=# select decode(null,null,1,2);
 decode 
--------
      1
(1 row)