drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sorabh Hamirwasia (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-5999) Add support for LATERAL join
Date Thu, 25 Jan 2018 03:24:00 GMT

    [ 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 &amp; 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)

Mime
View raw message