[CALCITE-5156] Support implicit integer type cast for IN Sub-query

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.39.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 17/May/22 02:33
• Updated: 23/Oct/24 14:43
• Resolved: 22/Oct/24 00:44

Description

The SQL:

select * from dept where deptno + 20 in (select deptno from dept);

Calcite returns the wrong answer.

but the SQL

 

select * from dept where deptno + 20 in (select cast(deptno as integer) from dept);

Calcite returns the correct answer.

So when we generate the RelNode, we can add the type cast.

Before the type cast:

LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
  LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(DEPTNO=[$0])
  LogicalTableScan(table=[[scott, DEPT]])
})])
    LogicalTableScan(table=[[scott, DEPT]])

After the type cast:

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]])

Same SQL includes:

select *
from dept
where deptno in (select sal-780 from emp)

Issue Links

• is blocked by: CALCITE-6592 Add test for RelMdPredicates pull up predicate from UNION when it's input predicates include NULL VALUE
• is duplicated by: CALCITE-5930 When sqlToRel is converted from in to join, condition '=' on both sides will have inconsistent data types.
• is related to: CALCITE-5769 Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
• is related to: CALCITE-2302 Implicit type cast support
• relates to: CALCITE-5743 Query gives incorrect result when COUNT appears in the correlated subquery select list
• relates to: CALCITE-5210 "type mismatch" litmus test failure during during SqlToRelConverter for group-by on `case` having `in` expression predicates exceeding SqlRelConverter.Config InSubQueryThreshold with nullable left-hand-side
• relates to: CALCITE-6435 SqlToRel conversion of IN expressions may lead to incorrect simplifications
• links to: GitHub Pull Request #2811