[
https://issues.apache.org/jira/browse/CALCITE-297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-297.
---------------------------------
Resolution: Works for Me
As noted, Calcite already does the right thing (as long as you choose Correlate
for the physical plan): compute the threshold, assign to a variable, use the
variable in a Filter.
> Processing a scalar sub-query as a Filter instead of a Join
> -----------------------------------------------------------
>
> Key: CALCITE-297
> URL: https://issues.apache.org/jira/browse/CALCITE-297
> Project: Calcite
> Issue Type: Bug
> Reporter: GitHub Import
> Priority: Major
> Labels: github-import, sub-query
>
> For the query below, it would be quite useful if Optiq could detect that the
> subquery in the HAVING clause is scalar and process it as a local filter on
> the outer 'nation' table instead of as a join.
> For comparison, I have provided the Postgres plan for the same query.
> select n.n_regionkey from nation n group by n.n_regionkey having
> n.n_regionkey > (select min(n2.n_regionkey) from nation n2);
> Optiq logical plan: (I have removed the cost information since that is not
> relevant):
> ProjectRel(n_regionkey=[$0]):
> FilterRel(condition=[>($0, $1)])
> JoinRel(condition=[true], joinType=[left]):
> AggregateRel(group=[{0}]):
> ProjectRel(n_regionkey=[$1]):
> EnumerableTableAccessRel(table=[[cp, tpch/nation.parquet]]):
> AggregateRel(group=[{}], EXPR$0=[MIN($0)]):
> ProjectRel(n_regionkey=[$1]):
> EnumerableTableAccessRel(table=[[cp, tpch/nation.parquet]]):
> Postgres plan:
> QUERY PLAN
> --------------------------------------------------------------------------
> HashAggregate (cost=24.92..25.55 rows=63 width=4)
> InitPlan 1 (returns $0)
> -> Aggregate (cost=12.38..12.39 rows=1 width=4)
> -> Seq Scan on nation n2 (cost=0.00..11.90 rows=190 width=4)
> -> Seq Scan on nation n (cost=0.00..12.38 rows=63 width=4)
> Filter: (n_regionkey > $0)
> (6 rows)
> Julian's response:
> I agree that would be useful.
> In Optiq we'd model that as a nested loop join. The outer loop (not shown in
> your Postgres plan) executes once and sets $0. Then the inner loop gets
> executed.
> See CorrelatorRel, which implements a join by setting a variable in an outer
> loop, and NestedLoopsJoinRule, which creates it. If you produce an
> implementation of CorrelatorRel in Drill (or in any convention -- performance
> is not an issue) you're done.
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/297
> Created by: [amansinha100|https://github.com/amansinha100]
> Labels:
> Created at: Tue Jun 03 03:35:50 CEST 2014
> State: open
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
