[
https://issues.apache.org/jira/browse/CALCITE-4805?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17420706#comment-17420706
]
duan xiong commented on CALCITE-4805:
-------------------------------------
[~julianhyde] [~donnyzone] Hi. Thank you reply.
1)CALCITE-373 sub-query is a query or ROW, not the _*SqlNodeList*_. That's
different. The In sub-query can be converted to OR condition Only When the
sub-query is _*SqlNodeList.*_
The relative code:
[https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1140]
2) I have checked the result in PostgreSQL. The test that was added in the
sub-query.iq has the right result.
3) When the SqlNodeList contains NULL, no matter the list size, they all can be
converted to Join. This is right behaviour, but not better. As the Calcite
code. Only the NULL makes this difference. In this scene, I think the Null and
other literals in this scene the only difference is 3-Valued Logic and the OR
condition can handle it well. So I delete the condition.
4) Because the code is legacy code, not a PR to track. So I guess this may be
caused by the OR condition that can't handle 3-Valued Logic well?
Expect your reply!
> The IN sub-query can't be converted to OR when value 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
>
> 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).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
