drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aman Sinha (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-5999) Add support for LATERAL join
Date Wed, 29 Nov 2017 17:30:00 GMT
Aman Sinha created DRILL-5999:

             Summary: Add support for LATERAL join
                 Key: DRILL-5999
                 URL: https://issues.apache.org/jira/browse/DRILL-5999
             Project: Apache Drill
          Issue Type: New Feature
          Components: Query Planning & Optimization
    Affects Versions: 1.11.0
            Reporter: Aman Sinha

The LATERAL keyword in SQL standard can precede a sub-SELECT FROM item. This allows the sub-SELECT
to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL,
each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

Calcite supports the LATERAL syntax.  In Drill, we should add support for it in the planning
and execution phase.  

The main motivation of supporting it is it makes it more expressive and performant to handling
complex types such as arrays and maps.  For instance, suppose you have a customer table which
contains 1 row per customer containing customer-id, name and an array of Orders corresponding
to each customer.   Suppose you want to find out for each customer what is the average order
amount.  This could be expressed as follows using SQL standard LATERAL and UNNEST syntax:
SELECT customer_name FROM customers c 
   LATERAL (SELECT AVG(order_amount) FROM UNNEST(c.orders));

The subquery may contain other operations such as filtering etc which operate on the output
of the  un-nested c.orders array.  The UNNEST operation is supported in Drill today using
FLATTEN operator.  More details of the use cases for LATERAL is available from existing product
documentations .. e.g see [1].   

[1] https://www.postgresql.org/docs/9.4/static/queries-table-expressions.html

This message was sent by Atlassian JIRA

View raw message