[ https://issues.apache.org/jira/browse/CALCITE-5156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17852427#comment-17852427 ]
xiong duan commented on CALCITE-5156: ------------------------------------- The PR has updated. The above problem has resolved in new version. > Support implicit number 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 > Assignee: xiong duan > Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > 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} > Same SQL includes: > {code:java} > select * > from dept > where deptno in (select sal-780 from emp){code} -- This message was sent by Atlassian Jira (v8.20.10#820010)