[
https://issues.apache.org/jira/browse/CALCITE-4875?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17444961#comment-17444961
]
Chunwei Lei commented on CALCITE-4875:
--------------------------------------
Oops, thank you for your kindly reminder, [~zabetak] . Sorry for the wrong
tip, [~nobigo] .
> 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)
