org.eigenbase.sql.fun
Class SqlCaseOperator

java.lang.Object
  org.eigenbase.sql.SqlOperator
      org.eigenbase.sql.fun.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.

Since:

Mar 14, 2004

Version:

$Id: //open/dev/farrago/src/org/eigenbase/sql/fun/SqlCaseOperator.java#15 $

Author:

Wael Chatila

Field Summary

private static SqlWriter.FrameType

CaseFrameType

Fields inherited from class org.eigenbase.sql.SqlOperator

MaxPrec, NL

Constructor Summary

SqlCaseOperator()

Method Summary

boolean	checkOperandTypes(SqlCallBinding callBinding, boolean throwOnFailure) Checks that the operand values in a SqlCall to this operator are valid.
SqlCall	createCall(SqlLiteral functionQualifier, SqlParserPos pos, SqlNode... operands) Creates a call to this operand with an array of operands.
SqlCase	createSwitchedCall(SqlParserPos pos, SqlNode caseIdentifier, SqlNodeList whenList, SqlNodeList thenList, SqlNode elseClause) Creates a call to the switched form of the case operator, viz: CASE caseIdentifier WHEN whenList[0] THEN thenList[0] WHEN whenList[1] THEN thenList[1] ... ELSE elseClause END
RelDataType	deriveType(SqlValidator validator, SqlValidatorScope scope, SqlCall call) Derives the type of a call to this operator.
SqlOperandCountRange	getOperandCountRange() Returns a constraint on the number of operands expected by this operator.
SqlSyntax	getSyntax() Returns the syntactic type of this operator.
RelDataType	inferReturnType(SqlOperatorBinding opBinding) Infers the return type of an invocation of this operator; only called after the number and types of operands have already been validated.
private RelDataType	inferTypeFromOperands(RelDataTypeFactory typeFactory, RelDataType[] argTypes)
private RelDataType	inferTypeFromValidator(SqlCallBinding callBinding)
void	unparse(SqlWriter writer, SqlNode[] operands, int leftPrec, int rightPrec) Writes a SQL representation of a call to this operator to a writer, including parentheses if the operators on either side are of greater precedence.
void	validateCall(SqlCall call, SqlValidator validator, SqlValidatorScope scope, SqlValidatorScope operandScope) Validates a call to this operator.

Methods inherited from class org.eigenbase.sql.SqlOperator

acceptCall, acceptCall, adjustType, argumentMustBeScalar, checkOperandCount, createCall, createCall, createCall, equals, getAllowedSignatures, getAllowedSignatures, getKind, getLeftPrec, getMonotonicity, getName, getOperandTypeChecker, getOperandTypeInference, getReturnTypeInference, getRightPrec, getSignatureTemplate, hashCode, inferReturnType, isAggregator, isDeterministic, isDynamicFunction, isName, leftPrec, preValidateCall, requiresDecimalExpansion, rewriteCall, rightPrec, toString, unparseListClause, unparseListClause, validateOperands

Methods inherited from class java.lang.Object

clone, finalize, getClass, notify, notifyAll, wait, wait, wait

Field Detail

CaseFrameType

private static final SqlWriter.FrameType CaseFrameType

Constructor Detail

SqlCaseOperator

public SqlCaseOperator()

Method Detail

validateCall

public void validateCall(SqlCall call,
                         SqlValidator validator,
                         SqlValidatorScope scope,
                         SqlValidatorScope operandScope)

Description copied from class: SqlOperator

Validates a call to this operator.

This method should not perform type-derivation or perform validation related related to types. That is done later, by SqlOperator.deriveType(SqlValidator, SqlValidatorScope, SqlCall). This method should focus on structural validation.

A typical implementation of this method first validates the operands, then performs some operator-specific logic. The default implementation just validates the operands.

This method is the default implementation of SqlCall.validate(org.eigenbase.sql.validate.SqlValidator, org.eigenbase.sql.validate.SqlValidatorScope); but note that some sub-classes of SqlCall never call this method.

Overrides:

validateCall in class SqlOperator

Parameters:

call - the call to this operator

validator - the active validator

scope - validator scope

operandScope - validator scope in which to validate operands to this call; usually equal to scope, but not always because some operators introduce new scopes

See Also:

SqlNode.validateExpr(SqlValidator, SqlValidatorScope), SqlOperator.deriveType(SqlValidator, SqlValidatorScope, SqlCall)

deriveType

public RelDataType deriveType(SqlValidator validator,
                              SqlValidatorScope scope,
                              SqlCall call)

Description copied from class: SqlOperator

Derives the type of a call to this operator.

This method is an intrinsic part of the validation process so, unlike SqlOperator.inferReturnType(org.eigenbase.sql.SqlOperatorBinding), specific operators would not typically override this method.

Overrides:

deriveType in class SqlOperator

Parameters:

validator - Validator

scope - Scope of validation

call - Call to this operator

Returns:

Type of call

checkOperandTypes

public boolean checkOperandTypes(SqlCallBinding callBinding,
                                 boolean throwOnFailure)

Description copied from class: SqlOperator

Checks that the operand values in a SqlCall to this operator are valid. Subclasses must either override this method or supply an instance of SqlOperandTypeChecker to the constructor.

Overrides:

checkOperandTypes in class SqlOperator

Parameters:

callBinding - description of call

throwOnFailure - whether to throw an exception if check fails (otherwise returns false in that case)

Returns:

whether check succeeded

inferReturnType

public RelDataType inferReturnType(SqlOperatorBinding opBinding)

Description copied from class: SqlOperator

Infers the return type of an invocation of this operator; only called after the number and types of operands have already been validated. Subclasses must either override this method or supply an instance of SqlReturnTypeInference to the constructor.

Overrides:

inferReturnType in class SqlOperator

Parameters:

opBinding - description of invocation (not necessarily a SqlCall)

Returns:

inferred return type

inferTypeFromValidator

private RelDataType inferTypeFromValidator(SqlCallBinding callBinding)

inferTypeFromOperands

private RelDataType inferTypeFromOperands(RelDataTypeFactory typeFactory,
                                          RelDataType[] argTypes)

getOperandCountRange

public SqlOperandCountRange getOperandCountRange()

Description copied from class: SqlOperator

Returns a constraint on the number of operands expected by this operator. Subclasses may override this method; when they don't, the range is derived from the SqlOperandTypeChecker associated with this operator.

Overrides:

getOperandCountRange in class SqlOperator

Returns:

acceptable range

getSyntax

public SqlSyntax getSyntax()

Description copied from class: SqlOperator

Returns the syntactic type of this operator.

Specified by:

getSyntax in class SqlOperator

createCall

public SqlCall createCall(SqlLiteral functionQualifier,
                          SqlParserPos pos,
                          SqlNode... operands)

Description copied from class: SqlOperator

Creates a call to this operand with an array of operands.

The position of the resulting call is the union of the pos and the positions of all of the operands.

Overrides:

createCall in class SqlOperator

Parameters:

functionQualifier - function qualifier (e.g. "DISTINCT"), may be

pos - parser position of the identifier of the call

operands - array of operands

createSwitchedCall

public SqlCase createSwitchedCall(SqlParserPos pos,
                                  SqlNode caseIdentifier,
                                  SqlNodeList whenList,
                                  SqlNodeList thenList,
                                  SqlNode elseClause)

Creates a call to the switched form of the case operator, viz:

CASE caseIdentifier
WHEN whenList[0] THEN thenList[0]
WHEN whenList[1] THEN thenList[1]
...
ELSE elseClause
END

unparse

public void unparse(SqlWriter writer,
                    SqlNode[] operands,
                    int leftPrec,
                    int rightPrec)

Description copied from class: SqlOperator

Writes a SQL representation of a call to this operator to a writer, including parentheses if the operators on either side are of greater precedence.

The default implementation of this method delegates to SqlSyntax.unparse(org.eigenbase.sql.SqlWriter, org.eigenbase.sql.SqlOperator, org.eigenbase.sql.SqlNode[], int, int).

Overrides:

unparse in class SqlOperator