[CALCITE-5680] Wrong plan for multiple IN sub-queries with only literal operands

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Runkang He
• Reporter: Runkang He
• Created: 29/Apr/23 02:02
• Updated: 27/Jul/23 10:22
• Resolved: 05/May/23 02:15

Description

When the query contains multiple IN sub-queries with only literal operands, and connected with OR predicate in WHERE clause, the result is wrong. The minimal reproducer is below:
SQL:

select empno from sales.empnullables
where 1 in (
  select deptno from sales.deptnullables where name = 'dept1')
or 2 in (
  select deptno from sales.deptnullables where name = 'dept2')

The Plan generated by calcite master branch: (Notice the bold part of IS NULL($2) in the downstream LogicalFilter)

LogicalProject(EMPNO=[$0])
  LogicalFilter(condition=[OR(CASE(IS NULL($2), false, =($1, false), null:BOOLEAN, IS NOT NULL($1), true, false), CASE(IS NULL(*$2*), false, =($1, false), null:BOOLEAN, IS NOT NULL($1), true, false))])
    LogicalJoin(condition=[true], joinType=[left])
      LogicalJoin(condition=[true], joinType=[left])
        LogicalProject(EMPNO=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
        LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
          LogicalAggregate(group=[{0}], c=[COUNT()])
            LogicalProject(cs=[IS NOT NULL($0)])
              LogicalFilter(condition=[OR(=(1, $0), IS NULL($0))])
                LogicalProject(DEPTNO=[$0])
                  LogicalFilter(condition=[=($1, 'dept1')])
                    LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
      LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
        LogicalAggregate(group=[{0}], c=[COUNT()])
          LogicalProject(cs=[IS NOT NULL($0)])
            LogicalFilter(condition=[OR(=(2, $0), IS NULL($0))])
              LogicalProject(DEPTNO=[$0])
                LogicalFilter(condition=[=($1, 'dept2')])
                  LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])

The wrong part is that when build the downstream LogicalFilter for the two sub-queries, the filter for the second sub-query is CASE(IS NULL($2), false, =($1, false), null:BOOLEAN, IS NOT NULL($1), true, false), notice that $2 should be the second sub-query's intermediate table field dt.c(which field index is $4), and $1 should be the second sub-query's intermediate table field dt.cs(which field index is $3), but now the actual reference is the first sub-query's, this leads to wrong plan, and wrong result.

The root cause is that intermediate table's field name is the same as the previous sub-query's, and when lookup intermediate table field, it always returns the previous one which is not belong to the current subquery.

To fix this issue, we should use both different intermediate table name and field name to identify the reference correctly. (Note that we make intermediate table name different in Calcite-5655)

Issue Links

• is related to: CALCITE-5655 Wrong plan for multiple IN/SOME sub-queries with OR predicate
• links to: GitHub Pull Request #3182