xiong duan created CALCITE-5156:
-----------------------------------
Summary: Support implicit type cast for IN Sub-query
Key: CALCITE-5156
URL: https://issues.apache.org/jira/browse/CALCITE-5156
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
The SQL:
{code:java}
select * from dept where deptno + 20 in (select deptno from dept);{code}
Calcite returns the wrong answer.
but the SQL
{code:java}
select * from dept where deptno + 20 in (select cast(deptno as integer) from
dept);{code}
Calcite returns the correct answer.
So when we generate the RelNode, we can add the type cast.
Before the type cast:
{noformat}
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[scott, DEPT]])
})])
LogicalTableScan(table=[[scott, DEPT]]){noformat}
After the type cast:
{noformat}
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(EXPR$0=[CAST($0):INTEGER NOT NULL])
LogicalTableScan(table=[[scott, DEPT]])
})])
LogicalTableScan(table=[[scott, DEPT]]){noformat}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
