[jira] [Created] (CALCITE-2921) nullif(null,y) throws exception in verification

["pengzhiwei (JIRA)" <ji...@apache.org>]

pengzhiwei created CALCITE-2921:
-----------------------------------

             Summary: 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


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) Add a new SqlOperator like "NullifOperator" and disable the translation from nullif to case-when.

Any suggestion is welcomed,Thanks!



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)