[CALCITE-3918] SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Fix Version/s: 1.23.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Haisheng Yuan
• Created: 11/Apr/20 23:26
• Updated: 28/May/21 10:11
• Resolved: 20/Apr/20 03:03

Description

Disable RelDecorrelator and run TpchTest.testQuery17(), SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.

EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
  EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
    EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR, 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
      EnumerableCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{3}])
        EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
          EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4], L_EXTENDEDPRICE=[$5])
            EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
          EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
            EnumerableTableScan(table=[[TPCH_01, PART]])
        EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0), null:JavaType(class java.lang.Long), $0), $1)):JavaType(class java.lang.Long))])
          EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)], agg#1=[COUNT($4)])
            EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
              EnumerableTableScan(table=[[TPCH_01, LINEITEM]])