Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-297

Processing a scalar sub-query as a Filter instead of a Join

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Works for Me
    • Affects Version/s: None
    • Fix Version/s: 1.18.0
    • Component/s: 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=[

      {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
      Labels:
      Created at: Tue Jun 03 03:35:50 CEST 2014
      State: open

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              github-import GitHub Import
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: