[CALCITE-4887] Filter subQuery remove not fully optimized

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.28.0
• Labels: pull-request-available
• Assignee: bingfeng.guo
• Reporter: bingfeng.guo
• Due: 15/Nov/21
• Created: 14/Nov/21 08:51
• Updated: 08/Sep/22 17:14

Description

for example:

SELECT 
  count(1) 
FROM 
  SSB.P_LINEORDER as P_LINEORDER 
  LEFT JOIN SSB.PART as PART ON P_LINEORDER.LO_PARTKEY = PART.P_PARTKEY 
  LEFT JOIN SSB.DATES as DATES ON P_LINEORDER.LO_ORDERDATE = DATES.D_DATEKEY 
  LEFT JOIN SSB.CUSTOMER as CUSTOMER ON P_LINEORDER.LO_CUSTKEY = CUSTOMER.C_CUSTKEY 
  LEFT JOIN SSB.SUPPLIER as SUPPLIER ON P_LINEORDER.LO_SUPPKEY = SUPPLIER.S_SUPPKEY 
WHERE 
  P_LINEORDER.LO_ORDERDATE = '1992-01-01' 
  or (
    P_LINEORDER.LO_ORDERDATE in (
      select 
        D_DATEKEY 
      from 
        SSB.DATES 
      where 
        DATES.D_DATEKEY = '1992-01-01'
    )
  ) 
LIMIT 
  500

 

plan is

 

LimitRel(ctx=[], fetch=[500])
    AggregateRel(group-set=[[]], groups=[null], EXPR$0=[COUNT()])
      ProjectRel(ctx=[])
        FilterRel(condition=[OR(=($5, 1992-01-01), CAST(AND(IS NOT NULL($65), <>($62, 0))):BOOLEAN)])
          JoinRel(condition=[=($5, $64)], joinType=[left])
            JoinRel(condition=[true], joinType=[inner])
              JoinRel(condition=[=($4, $55)], joinType=[left])
                JoinRel(condition=[=($2, $47)], joinType=[left])
                  JoinRel(condition=[=($5, $30)], joinType=[left])
                    JoinRel(condition=[=($3, $21)], joinType=[left])
                      TableScan(table=[[SSB, P_LINEORDER]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]])
                      TableScan(table=[[SSB, PART]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8]])
                    TableScan(table=[[SSB, DATES]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]])
                  TableScan(table=[[SSB, CUSTOMER]], fields=[[0, 1, 2, 3, 4, 5, 6, 7]])
                TableScan(table=[[SSB, SUPPLIER]], fields=[[0, 1, 2, 3, 4, 5, 6]])
              AggregateRel(group-set=[[]], groups=[null], c=[COUNT()], ck=[COUNT($0)])
                ProjectRel(D_DATEKEY=[$0])
                  FilterRel(condition=[=($0, 1992-01-01)])
                    TableScan(table=[[SSB, DATES]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]])
            AggregateRel(group-set=[[0, 1]], groups=[null])
              ProjectRel(D_DATEKEY=[$0], i=[true])
                FilterRel(condition=[=($0, 1992-01-01)])
                  TableScan(table=[[SSB, DATES]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]])

 

 

But the optimal plan should be

 

LimitRel(fetch=[500])
    AggregateRel(group-set=[[]], groups=[null], EXPR$0=[COUNT()])
      ProjectRel(ctx=[])
        FilterRel(condition=[=($5, 1992-01-01)])
          JoinRel(condition=[=($5, $62)], joinType=[inner])
            JoinRel(condition=[=($4, $55)], joinType=[left])
              JoinRel(condition=[=($2, $47)], joinType=[left])
                JoinRel(condition=[=($5, $30)], joinType=[left])
                  JoinRel(condition=[=($3, $21)], joinType=[left])
                    TableScan(table=[[SSB, P_LINEORDER]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]])
                    TableScan(table=[[SSB, PART]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8]])
                  TableScan(table=[[SSB, DATES]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]])
                TableScan(table=[[SSB, CUSTOMER]], fields=[[0, 1, 2, 3, 4, 5, 6, 7]])
              TableScan(table=[[SSB, SUPPLIER]], fields=[[0, 1, 2, 3, 4, 5, 6]])
            AggregateRel(group-set=[[0]], groups=[null])
              ProjectRel(D_DATEKEY=[$0])
                FilterRel(condition=[=($0, 1992-01-01)])
                  TableScan(table=[[SSB, DATES]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]])

 

 

 

Issue Links

• relates to: CALCITE-2071 Query with IN and OR in WHERE clause returns wrong result
• links to: GitHub Pull Request #2611