[CALCITE-1799] "OR ... IN" sub-query conversion is wrong

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.12.0
• Fix Version/s: 1.14.0
• Component/s: core
• Labels: sub-query
• Assignee: Julian Hyde
• Reporter: Gian Merlino
• Created: 22/May/17 07:51
• Updated: 02/Oct/17 14:31
• Resolved: 06/Jul/17 23:17

Description

This query:

select * from emp where deptno = 10 or deptno in (
    select dept.deptno from dept where deptno < 5)

Is converted to this by SqlToRelConverter:

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[OR(=($7, 10), true)])
    LogicalJoin(condition=[=($7, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}])
        LogicalProject(DEPTNO=[$0])
          LogicalFilter(condition=[<($0, 5)])
            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

But that's not right.

LogicalFilter(condition=[OR(=($7, 10), true)])

is always true and is in the wrong place anyway (it's applied after the inner join, where all the deptno = 10 records have already been removed).

Issue Links

• depends upon: CALCITE-816 Represent sub-query as a RexNode
• is related to: CALCITE-1874 Configurable SqlToRelConverter.Config in Frameworks
• relates to: CALCITE-816 Represent sub-query as a RexNode