[
https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16794114#comment-16794114
]
Danny Chan commented on CALCITE-2921:
-------------------------------------
[~julianhyde] I have tried NULLIF(CAST(NULL AS INTEGER), y) and it works
correctly.
While i think Mysql can do this cause it has implicit type cast, it cast the
null as the same data type of the other operands. But now calcite does not
support implicit type coercion, so an explicit is okey.
> nullif(null,y) throws exception in verification
> ------------------------------------------------
>
> Key: CALCITE-2921
> URL: https://issues.apache.org/jira/browse/CALCITE-2921
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.18.0
> Reporter: pengzhiwei
> Assignee: pengzhiwei
> Priority: Critical
>
> Currently calcite will translate "nullif(x,y)" to a case-when expression,just
> like "case when x = y then null else x".So when "x" is null literal,a
> exception throws out as follow:
> {code:java}
> ELSE clause or at least one THEN clause must be non-NULL
> {code}
> I have test in mysql,"nullif(null,y) works well.So I think we should allow
> this usage of "nullif".
> There are two ways to fix this issue:
> 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes:
>
> {code:java}
> if (!foundNotNull) {
> // according to the sql standard we can not have all of the THEN
> // statements and the ELSE returning null
> if (throwOnFailure) {
> throw callBinding.newError(RESOURCE.mustNotNullInElse());
> }
> return false;
> }{code}
> However, as the comment says, we cannot have all of the THEN and ELSE
> returning null.
> 2) Disable the translation from nullif to case-when and keep "nullif" as it
> is.
> Any suggestion is welcomed,Thanks!
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
