[
https://issues.apache.org/jira/browse/CALCITE-4887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17601935#comment-17601935
]
Ruben Q L commented on CALCITE-4887:
------------------------------------
It seems there has been no activity on this ticket for months, I am reseting
its fix version.
> 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
> Fix For: 1.32.0
>
> 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.10#820010)
