[jira] [Resolved] (CALCITE-297) Processing a scalar sub-query as a Filter instead of a Join

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ 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)