Details
-
New Feature
-
Status: In Progress
-
Major
-
Resolution: Unresolved
-
1.11.0
-
None
Description
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
Attachments
Issue Links
- depends upon
-
DRILL-6431 Unnest operator requires table and a single column alias to be specified.
- Resolved
-
DRILL-6456 Planner shouldn't create any exchanges on the right side of Lateral Join.
- Resolved
-
DRILL-6476 Generate explain plan which shows relation between Lateral and the corresponding Unnest.
- Resolved
-
DRILL-6530 JVM crash with a query involving multiple json files with one file having a schema change of one column from string to list
- Resolved
-
DRILL-6535 ClassCastException in Lateral Unnest queries when dealing with schema changed json data
- Resolved
-
DRILL-6542 IndexOutOfBoundsException for multilevel lateral queries with schema changed partitioned complex data
- Resolved
-
DRILL-6546 Allow unnest function with nested columns and complex expressions
- Resolved
-
DRILL-6548 IllegalStateException: Unexpected EMIT outcome received in buildSchema phase
- Resolved
-
DRILL-6553 Fix TopN for unnest operator
- Resolved
-
DRILL-6592 Unnest perf improvements - record batch sizer is called too frequently
- Resolved
-
DRILL-6649 Query with unnest of column from nested subquery fails
- Resolved
-
DRILL-6515 Render a linkage between the Unnest operator and its source operator
- Resolved
-
DRILL-6418 Handle Schema change in Unnest And Lateral for unnest field / non-unnest field
- Resolved
-
DRILL-6440 Fix ignored unit tests in unnest
- Resolved
-
DRILL-6479 Support for EMIT outcome in Hash Aggregate
- Resolved
-
DRILL-6503 Performance improvements in lateral
- Resolved
-
DRILL-6516 Support for EMIT outcome in streaming agg
- Resolved
-
DRILL-6545 Projection Push down into Lateral Join operator.
- Resolved
-
DRILL-6645 Transform TopN in Lateral Unnest pipeline to Sort and Limit.
- Resolved
-
DRILL-6502 Rename CorrelatePrel to LateralJoinPrel
- Resolved
-
DRILL-6321 Lateral Join: Planning changes - enable submitting physical plan
- Resolved
-
DRILL-6322 Lateral Join: Common changes - Add new iterOutcome, Operator types, MockRecordBatch for testing
- Resolved
-
DRILL-6323 Lateral Join - Initial implementation
- Resolved
-
DRILL-6324 Unnest - Initial Implementation
- Resolved
-
DRILL-6327 Update unary operators to handle IterOutcome.EMIT
- Resolved
-
DRILL-6419 E2E Integration test for Lateral&Unnest
- Resolved
-
DRILL-6420 Add Lateral and Unnest Keyword for highlighting on WebUI
- Resolved
-
DRILL-6446 Support for EMIT outcome in TopN
- Resolved
-
DRILL-6498 Support for EMIT outcome in ExternalSortBatch
- Resolved
- incorporates
-
DRILL-6674 Minor fixes to avoid auto boxing cost in logging in LateralJoinBatch
- Resolved
-
DRILL-6616 Batch Processing for Lateral/Unnest
- Resolved
-
DRILL-6729 Enable Unnest/Lateral Join feature by default
- Resolved
- relates to
-
DRILL-6440 Fix ignored unit tests in unnest
- Resolved
- requires
-
DRILL-6638 Fix TestE2EUnnestAndLateral tests introduced with DRILL-6546
- Resolved
-
DRILL-6653 Unsupported Schema change exception where there is no schema change in lateral Unnest queries
- Resolved
-
DRILL-6635 PartitionLimit for Lateral/Unnest
- Resolved
-
DRILL-6636 Planner side changes to use PartitionLimitBatch in place of LimitBatch
- Resolved
-
DRILL-6652 PartitionLimit changes for Lateral and Unnest
- Resolved
- links to