[CALCITE-2921] Allow NULL as argument to function calls

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.18.0
• Fix Version/s: 1.21.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: pengzhiwei
• Created: 14/Mar/19 12:09
• Updated: 10/Sep/19 21:35
• Resolved: 29/Aug/19 11:53

Description

Currently nullif(null,y) throws exception in verification. This is because translates 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:

 ELSE clause or at least one THEN clause must be non-NULL

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:     

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;
}

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!

Issue Links

• is related to: CALCITE-1864 Allow NULL literal as argument
• is related to: CALCITE-3198 Enhance RexSimplify to handle (x<>a or x<>b)
• is related to: CALCITE-3217 Support "SELECT NULL"
• relates to: CALCITE-2302 Implicit type cast support
• links to: GitHub Pull Request #1117