[
https://issues.apache.org/jira/browse/CALCITE-4875?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
duan xiong updated CALCITE-4875:
--------------------------------
Description:
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)
was:
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)
> 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
> Reporter: Jay Narale
> Priority: Major
> Labels: pull-request-available
> Time Spent: 2h 40m
> 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.3.4#803005)
