[DRILL-5999] Add support for LATERAL join

• Type: New Feature
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.11.0
• Component/s: Query Planning & Optimization
• Labels: doc-impacting
• Assignee: Parth Chandra
• Reporter: Aman Sinha
• Created: 29/Nov/17 17:29
• Updated: 04/Sep/18 20:53

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

Issue Links

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