[jira] [Closed] (CALCITE-4875) NVL Function Incorrectly changes nullability field of its operands

["Rui Wang (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4875?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rui Wang closed CALCITE-4875.
-----------------------------

> NVL Function Incorrectly changes nullability field of its operands
> ------------------------------------------------------------------
>
>                 Key: CALCITE-4875
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4875
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.28.0
>            Reporter: Jay Narale
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.29.0
>
>          Time Spent: 6h 50m
>  Remaining Estimate: 0h
>
> We rewrite the NVL function in _org/apache/calcite/sql2rel/StandardConvertletTable.java:303_  , during the rewrite we currently override the operands' nullability to be NOT NULL which is not needed. This causes issues if that operand is pushdown , since the input of that operand need not be not nullable whereas we forced that operand to be Not nullable.
> We rewrite the nvl function  [ NVL(op1, op2)  to CASE ( IS NOT NULL (op1), op1, op2) ]
> Previously we were wrongly setting final op1, op2 nullability to NOT NULL
> For Example for the query
> select nvl("name", 'undefined') FROM "hr"."emps" 
> Using the test framework, (Column "name" is nullable)
> The generated plan before this change is:
> {code:java}
> EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t2)], expr#6=[CAST($t2):VARCHAR NOT NULL], expr#7=['undefined':VARCHAR], expr#8=[CASE($t5, $t6, $t7)], EXPR$0=[$t8])
>  EnumerableTableScan(table=[[hr, emps]])
> {code}
> After this change:
> {code:java}
> EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t2)], expr#6=[CAST($t2):VARCHAR], expr#7= ['undefined':VARCHAR], expr#8=[CASE($t5, $t6, $t7)], EXPR$0=[$t8])
>  EnumerableTableScan(table=[[hr, emps]])
>  
> {code}
> If we look at expr#8 , its the CASE described earlier and expr#6 is the "name" column.
> As described with this change we can assert that the nullability is preserved as nullable as required ( expr#6 is set to Nullable)



--
This message was sent by Atlassian Jira
(v8.20.1#820001)