[jira] [Commented] (CALCITE-2921) Allow NULL as argument to function calls

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

    [ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16898442#comment-16898442 ] 

Julian Hyde commented on CALCITE-2921:
--------------------------------------

Reviewing now.

In a few places, some plans were improved by this change even though the original SQL did not include raw NULL literals. What was happening was that we were creating raw NULL literals internally (e.g. {{SubQueryRemoveRule}} calls {{RelBuilder.literal(null)}}). I have amended the change to make this code create typed NULLs.

> Allow NULL as argument to function calls
> ----------------------------------------
>
>                 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
>              Labels: pull-request-available
>          Time Spent: 1h 10m
>  Remaining Estimate: 0h
>
> 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:
> {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.14#76016)