bingfeng.guo created CALCITE-4887: ------------------------------------- Summary: Filter subQuery remove tot fully optimized Key: CALCITE-4887 URL: https://issues.apache.org/jira/browse/CALCITE-4887 Project: Calcite Issue Type: Improvement Reporter: bingfeng.guo
for example: ```sql 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]]) ``` -- This message was sent by Atlassian Jira (v8.20.1#820001)