[
https://issues.apache.org/jira/browse/CALCITE-4805?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
duan xiong updated CALCITE-4805:
--------------------------------
Description:
Since CALCITE-373 Calcite has converted "x IN (1, 2)" to "x = 1 OR x = 2" but
it still converts "x IN (1, NULL)" to "x IN (VALUES 1, NULL)" and that, since
some bugs have been fixed, there's no good reason for the difference.
For example:
{code:java}
select * from emp where deptno in (null,2){code}
Now the RelNode is:
{code:java}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalValues(tuples=[[{ null }, { 2 }]]){code}
The RelNode should be:
{code:java}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[OR(=($7, null), =($7, 2))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
was:
for example:
{code:java}
select * from emp where deptno in (null,2){code}
Now the RelNode is:
{code:java}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalValues(tuples=[[{ null }, { 2 }]]){code}
The RelNode should be:
{code:java}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[OR(=($7, null), =($7, 2))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
So I think when the sub-query value list size is under the
threshold(IN_SUB_QUERY_THRESHOLD), they can be converted to OR(no matter have
the NullLiteral or not).
> Calcite should convert a small IN-list as if the user had written OR, even if
> the IN-list contains NULL
> -------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4805
> URL: https://issues.apache.org/jira/browse/CALCITE-4805
> Project: Calcite
> Issue Type: Bug
> Reporter: duan xiong
> Assignee: duan xiong
> Priority: Major
>
> Since CALCITE-373 Calcite has converted "x IN (1, 2)" to "x = 1 OR x = 2" but
> it still converts "x IN (1, NULL)" to "x IN (VALUES 1, NULL)" and that, since
> some bugs have been fixed, there's no good reason for the difference.
> For example:
> {code:java}
> select * from emp where deptno in (null,2){code}
> Now the RelNode is:
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=($7, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalValues(tuples=[[{ null }, { 2 }]]){code}
> The RelNode should be:
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[OR(=($7, null), =($7, 2))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
