Package org.apache.calcite.sql.fun

Class SqlCaseOperator

  • public class SqlCaseOperator
extends SqlOperator
    An operator describing a CASE, NULLIF or COALESCE expression. All of these forms are normalized at parse time to a to a simple CASE statement like this: 
    CASE
   WHEN <when expression_0> THEN <then expression_0>
   WHEN <when expression_1> THEN <then expression_1>
   ...
   WHEN <when expression_N> THEN <then expression_N>
   ELSE <else expression>
 END

    The switched form of the CASE statement is normalized to the simple form by inserting calls to the = operator. For example,

    CASE x + y
   WHEN 1 THEN 'fee'
   WHEN 2 THEN 'fie'
   ELSE 'foe'
 END

    becomes

    CASE
 WHEN Equals(x + y, 1) THEN 'fee'
 WHEN Equals(x + y, 2) THEN 'fie'
 ELSE 'foe'
 END

    REVIEW jhyde 2004/3/19 Does Equals handle NULL semantics correctly?

    COALESCE(x, y, z) becomes

    CASE
 WHEN x IS NOT NULL THEN x
 WHEN y IS NOT NULL THEN y
 ELSE z
 END

    NULLIF(x, -1) becomes

    CASE
 WHEN x = -1 THEN NULL
 ELSE x
 END

    Note that some of these normalizations cause expressions to be duplicated. This may make it more difficult to write optimizer rules (because the rules will have to deduce that expressions are equivalent). It also requires that some part of the planning process (probably the generator of the calculator program) does common sub-expression elimination.

    REVIEW jhyde 2004/3/19. Expanding expressions at parse time has some other drawbacks. It is more difficult to give meaningful validation errors: given COALESCE(DATE '2004-03-18', 3.5), do we issue a type-checking error against a CASE operator? Second, I'd like to use the SqlNode object model to generate SQL to send to 3rd-party databases, but there's now no way to represent a call to COALESCE or NULLIF. All in all, it would be better to have operators for COALESCE, NULLIF, and both simple and switched forms of CASE, then translate to simple CASE when building the RexNode tree.

    The arguments are physically represented as follows:

    • The when expressions are stored in a SqlNodeList whenList.
    • The then expressions are stored in a SqlNodeList thenList.
    • The else expression is stored as a regular SqlNode.