This section describes the SQL-compliant conditional expressions available in LightDB.
If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a server-side function in a more expressive programming language.
Although COALESCE, GREATEST, and
LEAST are syntactically similar to functions, they are
not ordinary functions, and thus cannot be used with explicit
VARIADIC array arguments.
CASE
The SQL CASE expression is a
generic conditional expression, similar to if/else statements in
other programming languages:
CASE WHENconditionTHENresult[WHEN ...] [ELSEresult] 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:
CASEexpressionWHENvalueTHENresult[WHEN ...] [ELSEresult] 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;
As described in Section 4.2.15, there are various
situations in which subexpressions of an expression are evaluated at
different times, so that the principle that “CASE
evaluates only necessary subexpressions” is not ironclad. For
example a constant 1/0 subexpression will usually result in
a division-by-zero failure at planning time, even if it's within
a CASE arm that would never be entered at run time.
COALESCECOALESCE(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.
NULLIFNULLIF(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= 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 and LEASTGREATEST(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 and NVL2NVL(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, bit nvl | bit | bit, blob nvl | bit | bit, clob nvl | bit | bit, date nvl | bit | bit, double precision nvl | bit | bit, integer nvl | bit | bit, money nvl | bit | bit, numeric nvl | bit | bit, oracle.date nvl | bit | bit, text nvl | bit | bit, timestamp without time zone nvl | bit | bit, timestamp with time zone nvl | bit | bit, time without time zone nvl | blob | blob, bit nvl | blob | blob, blob nvl | blob | blob, clob nvl | blob | blob, date nvl | blob | blob, double precision nvl | blob | blob, integer nvl | blob | blob, money nvl | blob | blob, numeric nvl | blob | blob, oracle.date nvl | blob | blob, text nvl | blob | blob, timestamp without time zone nvl | blob | blob, timestamp with time zone nvl | blob | blob, time without time zone nvl | clob | clob, bit nvl | clob | clob, blob nvl | clob | clob, clob nvl | clob | clob, date nvl | clob | clob, double precision nvl | clob | clob, integer nvl | clob | clob, money nvl | clob | clob, numeric nvl | clob | clob, oracle.date nvl | clob | clob, text nvl | clob | clob, timestamp without time zone nvl | clob | clob, timestamp with time zone nvl | clob | clob, time without time zone nvl | date | date, bit nvl | date | date, blob nvl | date | date, clob nvl | date | date, date nvl | date | date, double precision nvl | date | date, integer nvl | date | date, money nvl | date | date, numeric nvl | date | date, oracle.date nvl | date | date, text nvl | date | date, timestamp without time zone nvl | date | date, timestamp with time zone nvl | date | date, time without time zone nvl | numeric | double precision, bit nvl | numeric | double precision, blob nvl | numeric | double precision, clob nvl | numeric | double precision, date nvl | numeric | double precision, double precision nvl | numeric | double precision, integer nvl | numeric | double precision, money nvl | numeric | double precision, numeric nvl | numeric | double precision, oracle.date nvl | numeric | double precision, text nvl | numeric | double precision, timestamp without time zone nvl | numeric | double precision, timestamp with time zone nvl | numeric | double precision, time without time zone nvl | numeric | integer, bit nvl | numeric | integer, blob nvl | numeric | integer, clob nvl | numeric | integer, date nvl | numeric | integer, double precision nvl | numeric | integer, integer nvl | numeric | integer, money nvl | numeric | integer, numeric nvl | numeric | integer, oracle.date nvl | numeric | integer, text nvl | numeric | integer, timestamp without time zone nvl | numeric | integer, timestamp with time zone nvl | numeric | integer, time without time zone nvl | money | money, bit nvl | money | money, blob nvl | money | money, clob nvl | money | money, date nvl | money | money, double precision nvl | money | money, integer nvl | money | money, money nvl | money | money, numeric nvl | money | money, oracle.date nvl | money | money, text nvl | money | money, timestamp without time zone nvl | money | money, timestamp with time zone nvl | money | money, time without time zone nvl | numeric | numeric, bit nvl | numeric | numeric, blob nvl | numeric | numeric, clob nvl | numeric | numeric, date nvl | numeric | numeric, double precision nvl | numeric | numeric, integer nvl | numeric | numeric, money nvl | numeric | numeric, numeric nvl | numeric | numeric, oracle.date nvl | numeric | numeric, text nvl | numeric | numeric, timestamp without time zone nvl | numeric | numeric, timestamp with time zone nvl | numeric | numeric, time without time zone nvl | oracle.date | oracle.date, bit nvl | oracle.date | oracle.date, blob nvl | oracle.date | oracle.date, clob nvl | oracle.date | oracle.date, date nvl | oracle.date | oracle.date, double precision nvl | oracle.date | oracle.date, integer nvl | oracle.date | oracle.date, money nvl | oracle.date | oracle.date, numeric nvl | oracle.date | oracle.date, oracle.date nvl | oracle.date | oracle.date, text nvl | oracle.date | oracle.date, timestamp without time zone nvl | oracle.date | oracle.date, timestamp with time zone nvl | oracle.date | oracle.date, time without time zone nvl | text | text, bit nvl | text | text, blob nvl | text | text, clob nvl | text | text, date nvl | text | text, double precision nvl | text | text, integer nvl | text | text, money nvl | text | text, numeric nvl | text | text, oracle.date nvl | text | text, text nvl | text | text, timestamp without time zone nvl | text | text, timestamp with time zone nvl | text | text, time without time zone nvl | timestamp without time zone | timestamp without time zone, bit nvl | timestamp without time zone | timestamp without time zone, blob nvl | timestamp without time zone | timestamp without time zone, clob nvl | timestamp without time zone | timestamp without time zone, date nvl | timestamp without time zone | timestamp without time zone, double precision nvl | timestamp without time zone | timestamp without time zone, integer nvl | timestamp without time zone | timestamp without time zone, money nvl | timestamp without time zone | timestamp without time zone, numeric nvl | timestamp without time zone | timestamp without time zone, oracle.date nvl | timestamp without time zone | timestamp without time zone, text nvl | timestamp without time zone | timestamp without time zone, timestamp without time zone nvl | timestamp without time zone | timestamp without time zone, timestamp with time zone nvl | timestamp without time zone | timestamp without time zone, time without time zone nvl | timestamp with time zone | timestamp with time zone, bit nvl | timestamp with time zone | timestamp with time zone, blob nvl | timestamp with time zone | timestamp with time zone, clob nvl | timestamp with time zone | timestamp with time zone, date nvl | timestamp with time zone | timestamp with time zone, double precision nvl | timestamp with time zone | timestamp with time zone, integer nvl | timestamp with time zone | timestamp with time zone, money nvl | timestamp with time zone | timestamp with time zone, numeric nvl | timestamp with time zone | timestamp with time zone, oracle.date nvl | timestamp with time zone | timestamp with time zone, text nvl | timestamp with time zone | timestamp with time zone, timestamp without time zone nvl | timestamp with time zone | timestamp with time zone, timestamp with time zone nvl | timestamp with time zone | timestamp with time zone, time without time zone nvl | time without time zone | time without time zone, bit nvl | time without time zone | time without time zone, blob nvl | time without time zone | time without time zone, clob nvl | time without time zone | time without time zone, date nvl | time without time zone | time without time zone, double precision nvl | time without time zone | time without time zone, integer nvl | time without time zone | time without time zone, money nvl | time without time zone | time without time zone, numeric nvl | time without time zone | time without time zone, oracle.date nvl | time without time zone | time without time zone, text nvl | time without time zone | time without time zone, timestamp without time zone nvl | time without time zone | time without time zone, timestamp with time zone nvl | time without time zone | time without time zone, time without time zone
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, text) 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, null is actually of unknown type and returns the data type of expr2,
so the function called is nvl(numeric, numeric) so the function returns the numeric type.
lightdb@test=# select pg_typeof(nvl(null, 1)), nvl(null, 1); pg_typeof | nvl -----------+----- numeric | 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_nvl; 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, numeric), so the function returns the text type
lightdb@test=# select pg_typeof(nvl(ltrim(func, 'nvl'), 1)), nvl(ltrim(func, 'nvl'), 1) from test_nvl; pg_typeof | nvl -----------+----- text | 1 (1 row)
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.
DECODE
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)