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:
{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
(v6.4.14#64029)
