Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2921

Allow NULL as argument to function calls

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.18.0
    • Fix Version/s: 1.21.0
    • Component/s: core

      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!

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                pzw2018 pengzhiwei
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 10m
                  2h 10m