[ https://issues.apache.org/jira/browse/CALCITE-4887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Liya Fan updated CALCITE-4887: ------------------------------ Fix Version/s: (was: 1.30.0) > Filter subQuery remove not fully optimized > ------------------------------------------ > > Key: CALCITE-4887 > URL: https://issues.apache.org/jira/browse/CALCITE-4887 > Project: Calcite > Issue Type: Improvement > Affects Versions: 1.28.0 > Reporter: bingfeng.guo > Assignee: bingfeng.guo > Priority: Major > Labels: pull-request-available > Time Spent: 2.5h > Remaining Estimate: 0h > > for example: > {quote}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 > {quote} > > plan is > > {quote}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]]) > {quote} > > > But the optimal plan should be > {quote} > 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]]) > > {quote} > > -- This message was sent by Atlassian Jira (v8.20.1#820001)