[
https://issues.apache.org/jira/browse/DRILL-5999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16338669#comment-16338669
]
Sorabh Hamirwasia commented on DRILL-5999:
------------------------------------------
This document discuss the detailed design for supporting LATERAL and UNNEST in
Drill. It is still WIP, but feedbacks are welcome.
> 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
> Assignee: Aman Sinha
> Priority: Major
> Labels: doc-impacting
>
> 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:
> {noformat}
> SELECT customer_name FROM customers c
> LATERAL (SELECT AVG(order_amount) FROM UNNEST(c.orders));
> {noformat}
> 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
(v7.6.3#76005)
