Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Works for Me
-
None
-
None
Description
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=[
]):
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
Labels:
Created at: Tue Jun 03 03:35:50 CEST 2014
State: open