[
https://issues.apache.org/jira/browse/CALCITE-4758?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17901177#comment-17901177
]
xiong duan commented on CALCITE-4758:
-------------------------------------
Because when we use VALUES expression and this subquery will become
RexSubQuery. Then SubQueryRemoveRule can hanle it right.
If we only use SqlNodeList, Then the subquery will be handle by
SqlToRelConverter#substituteSubQuery(Regardless of whether we have set expand
or not).
Therefore, there are two ways to fix this issue:
one is to continue improving the processing logic in substituteSubQuery, and
the other is to convert it into VALUES and become a RexSubQuery. Because the
processing logic in substituteSubQuery is currently used less and there are
more issues, my PR uses the second method to fix this problem.
The changes here mainly affected the previous versions of IN and some NOT IN
queries. Due to the incorrect return results of SOME and ALL, we will ignore
them here. Previously, after converting to SQL ToRel, we could directly convert
the corresponding subqueries into Join, but now it has become a RexSubquery.
> When SOME sub-query is SqlNodeList and converted to VALUES, Calcite returns
> incorrect result
> --------------------------------------------------------------------------------------------
>
> Key: CALCITE-4758
> URL: https://issues.apache.org/jira/browse/CALCITE-4758
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: xiong duan
> Assignee: xiong duan
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> As discussed in the comments: The SQL
> {code:java}
> select * from "scott".emp where sal > some (4000, 2000)
> {code}
> is illegal SQL. So we should throw an exception instead of return a wrong
> result.
> The SQL include:
> SOME and ALL:
> {code:java}
> 1: select deptno from emp where deptno > some (23, 56);
> 2: select deptno from emp where deptno > some (cast(23 as integer), cast(56
> as integer));
> 3: select deptno from emp where deptno > all (cast(23 as integer), cast(56 as
> integer));
> 4: select deptno from emp where deptno > all (23, 56){code}
> NOT IN:
> {code:java}
> select deptno from emp where deptno not in (60, 56);
> DEPTNO
> 10
> 10
> 20
> 30
> 30
> 50
> 50
> null
> !ok{code}
> This case can't handle NULL value.
> But when we use the VALUES expression, SQL always can return the correct
> result. The SQL like:
> {code:java}
> select deptno from emp where deptno > some (VALUES (23), (56));
> DEPTNO
> 30
> 30
> 50
> 50
> 60
> !ok{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
