It’s all consistent - really! Consider ‘x OR NOT y’. Is ‘OR NOT’ an operator? Of course not. ‘NOT’ is a prefix operator, and ‘OR’ applies to its result.
Similarly, ‘x JOIN LATERAL y ON (c)’, the ‘LATERAL’ applies to ‘y’ more, and only later does the ‘JOIN’ kick in. ‘LATERAL’ can apply to any table-like object, such as a sub-query. So ‘x, LATERAL y’ fits with that model. (In fact, we think of ‘,’ as short-hand for ‘CROSS JOIN’.) CROSS APPLY and OUTER APPLY are really useful, and Oracle/Microsoft designed them well, but they are not fundamental. Calcite implements them as syntactic sugar — i.e. a rewrite at an early stage to ‘CROSS JOIN LATERAL’ — and I think that’s how Drill should do it too. LATERAL is fundamental but it’s hard for end-users to understand, so it’s better if end-users use UNNEST and CROSS/OUTER APPLY for most purposes. Julian > On Feb 2, 2018, at 9:08 AM, Aman Sinha <amansi...@apache.org> wrote: > > Internally, within the query engine both LATERAL and CROSS/OUTER APPLY > would be essentially doing the same work. > > After thinking about this some more, from the syntax perspective I do see > Julian's point about the CROSS/OUTER APPLY having some advantages over > LATERAL. > > In particular, with LATERAL there are a few inconsistencies: (I am not > sure why the SQL standards committee did not define this more crisply) > > Inner Join versions (equivalent to CROSS APPLY) ...there are 2 > alternatives: > 1. SELECT .. FROM t1, LATERAL (SELECT ... FROM UNNEST(t1.orders)) ; > 2. SELECT .. FROM t1 INNER JOIN LATERAL (SELECT .. FROM > UNNEST(t1.orders)) on TRUE; > > Outer Join version (equivalent to OUTER APPLY): > 3. SELECT .. FROM t1 LEFT JOIN LATERAL (SELECT ... FROM > UNNEST(t1.orders)) on TRUE ; > > I can see 3 inconsistencies : > a) In the first query there is a 'comma' separating the table with LATERAL > because LATERAL appears as a prefix operator acting on the subquery > whereas in the 2nd and 3rd queries, it appears as join qualifier and > there is no 'comma'. This gives the impression that it is a prefix in 1 > but a binary operator in 2 and 3. > b) For the outer join case, there is only one way to express the query > whereas with inner join there are 2 alternatives. > c) The 'ON TRUE' clause is needed in the 'JOIN' versions 2 and 3, but not > needed in 1. > > > In comparison, the CROSS/OUTER APPLY is less ambiguous : there is only one > way to express a CROSS or OUTER APPLY. It does not involve the 'JOIN' > clause, > so there is no need for the 'ON' clause either. It also makes it explicit > that the APPLY keyword is about applying a table function. > > Couple of disadvantages of the CROSS/OUTER APPLY: > a) it is not official SQL standard, but as Julian said, it falls in the > 'gray area'. > b) If Drill supports these, the SqlConformance setting in Calcite would > need to be customized..currently we use DEFAULT. > > Overall, seems the advantages of CROSS/OUTER APPLY outweigh the > disadvantages, so we could go with that. > > > -Aman > > > > > On Tue, Jan 30, 2018 at 4:42 PM, Julian Hyde <jh...@apache.org > <mailto:jh...@apache.org>> wrote: > >> There are a few things in the gray area between the official standard and >> the de facto standard. CROSS/OUTER APPLY is well thought out, does not >> conflict with standard SQL, and has a couple of big vendors behind it. I >> think it’s safe to add it. (Calcite has a conformance setting so that >> someone can disable it if they choose.) >> >> SQL Server goes a bit further, in that allows you to invoke a UDF without >> the TABLE keyword. We do not allow that in Calcite, at this point, because >> it could be ambiguous[2]. >> >> I recommend that you do not start building key features on LATERAL. >> LATERAL is a concept that is tricky for users to get their heads around - >> it is really a hack that subtly adjusts the namespaces that the SQL >> validator uses when resolving table aliases inside a join. It’s not >> inherently about unnesting data or calling functions - in fact LATERAL is >> implicit if you use UNNEST. The less your end users need to type LATERAL, >> the better. >> >> Julian >> >> [2] https://issues.apache.org/jira/browse/CALCITE-1490 < >> https://issues.apache.org/jira/browse/CALCITE-1490 >> <https://issues.apache.org/jira/browse/CALCITE-1490>> >> >>> On Jan 30, 2018, at 12:20 PM, Chunhui Shi <c...@mapr.com> wrote: >>> >>> Hi Julian, I think CROSS APPLY and OUTER APPLY are what we want and we >> have discussed internally. the only problem is, they are not standard SQL >> although they are supported in SQL server and Oracle 12. Since SQL syntax >> does not have way to "invoke table function for each row", we have to >> choose between using APPLY or overloading the meaning of LATERAL as in the >> current document attached in the JIRA. Which way you think is the better >> way? >>> >>> >>> Thanks, >>> >>> Chunhui >>> >>> ________________________________ >>> From: Julian Hyde <jh...@apache.org> >>> Sent: Tuesday, January 30, 2018 12:01:47 PM >>> To: dev@drill.apache.org >>> Subject: Re: LATERAL and UNNEST support for Drill >>> >>> LATERAL is a prefix operator not a binary operator, so I believe you are >> missing a comma: >>> >>>> FROM t1 LATERAL UNNEST (t1.array1), UNNEST (t1.array2) >>> >>> should be >>> >>>> FROM t1, LATERAL UNNEST (t1.array1), LATERAL UNNEST (t1.array2) >>> >>> I agree with your remarks about the extra power of putting UNNEST in the >> FROM clause (per the standard) versus the SELECT clause (per PostgreSQL). >>> >>> Note that Calcite supports CROSS APPLY and OUTER APPLY[1]. This is >> useful when you want to apply a table function for each row of a table. It >> is just syntactic sugar for LATERAL TABLE so you may get it virtually for >> free. >>> >>> Julian >>> >>> >>> [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__issues. >> apache.org_jira_browse_CALCITE-2D1472&d=DwIFAg&c=cskdkSMqhcnjZxdQVpwTXg&r= >> FCGQb-L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m= >> 9Y08i3YgrresMOxi7InbjxT0WSHQkcPjJufQWLI9PGk&s= >> PfJfEyQhvXOSwuTo04m94qSHfz2KHZrR2WPazXpUl6g&e= <https://urldefense. >> proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_ >> browse_CALCITE-2D1472&d=DwIFAg&c=cskdkSMqhcnjZxdQVpwTXg&r=FCGQb- >> L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m=9Y08i3YgrresMOxi7InbjxT0WSHQkc >> PjJufQWLI9PGk&s=PfJfEyQhvXOSwuTo04m94qSHfz2KHZrR2WPazXpUl6g&e=> >>> >>> >>> >>>> On Jan 29, 2018, at 8:58 AM, Sorabh Hamirwasia <shamirwa...@mapr.com> >> wrote: >>>> >>>> Hi Ted, >>>> Thanks for you question. Array type aggregator is not planned along >> with this project. But probably after this is done we can look into it. >>>> >>>> Thanks, >>>> Sorabh >>>> >>>> Get Outlook for iOS<https://urldefense.proofpoint.com/v2/url?u=https- >> 3A__aka.ms_o0ukef&d=DwIFAg&c=cskdkSMqhcnjZxdQVpwTXg&r=FCGQb- >> L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m=9Y08i3YgrresMOxi7InbjxT0WSHQkc >> PjJufQWLI9PGk&s=InfpmexAnhHoPUeNA7M-E8qIORMLXwvsqDfFAA69glg&e=> >>>> ________________________________ >>>> From: Ted Dunning <ted.dunn...@gmail.com> >>>> Sent: Sunday, January 28, 2018 10:30:30 PM >>>> To: dev@drill.apache.org >>>> Cc: Chunhui Shi; Parth Chandra; Aman Sinha; Sorabh Hamirwasia >>>> Subject: Re: LATERAL and UNNEST support for Drill >>>> >>>> >>>> I haven't looked at the design doc, but this is a great thing to have. >>>> >>>> Would you be building something to do the inverse as well? >>>> >>>> Something like an aggregator such as array_collect, perhaps? >>>> >>>> >>>> >>>> On Thu, Jan 25, 2018 at 2:56 PM, Sorabh Hamirwasia < >> sohami.apa...@gmail.com<mailto:sohami.apa...@gmail.com>> wrote: >>>> Hi All, >>>> >>>> We (people in cc list) have been looking into design for support of >> LATERAL >>>> and UNNEST within Drill. With upgrade of Calcite to 1.15, these keywords >>>> are supported in Calcite too. As a first cut we have created a design >>>> document which proposes the changes and limitation's for this project. >>>> There are still few items which are in progress. I am sharing the JIRA >>>> details along with link to design document below. Please feel free to >> take >>>> a look and provide any feedback. >>>> >>>> >>>> DRILL-5999 <https://urldefense.proofpoint.com/v2/url?u=https- >> 3A__issues.apache.org_jira_browse_DRILL-2D5999&d=DwIFAg& >> c=cskdkSMqhcnjZxdQVpwTXg&r=FCGQb-L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m= >> 9Y08i3YgrresMOxi7InbjxT0WSHQkcPjJufQWLI9PGk&s=v0h- >> mUzhxxODVbYyAScphlkT0gnnF6vdvLAiaND2JvY&e=<https:// >> urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache. >> org_jira_browse_DRILL-2D5999&d=DwMFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r= >> gRpEl0WzXE3EMrwj0KFbZXGXRyadOthF2jlYxvhTlQg&m= >> iKu0hCGDHFbZsyzbsmTFjCYxYuLB4FUf26dimMQ8ErE&s=s-Ja1U7TeOgi96_ >> QzCmtlKlV9S8uvtBgjfywbJKp-Tw&e=>> >>>> >>>> Design Document >>>> <https://urldefense.proofpoint.com/v2/url?u=https- >> 3A__docs.google.com_document_d_1-2DRCIJ0F7VwAqOxkVB305zADwtX- >> 2DOS43Qj2kUmIILUaQ_edit-3Fusp-3Dsharing&d=DwIFAg&c= >> cskdkSMqhcnjZxdQVpwTXg&r=FCGQb-L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m= >> 9Y08i3YgrresMOxi7InbjxT0WSHQkcPjJufQWLI9PGk&s=30KoGGEUntrnoT- >> iDtbBdvnQKz25w3l-Op_ksZVNhRA&e=<https://urldefense. >> proofpoint.com/v2/url?u=https-3A__docs.google.com_document_d_1- >> 2DRCIJ0F7VwAqOxkVB305zADwtX-2DOS43Qj2kUmIILUaQ_edit-3Fusp- >> 3Dsharing&d=DwMFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r= >> gRpEl0WzXE3EMrwj0KFbZXGXRyadOthF2jlYxvhTlQg&m= >> iKu0hCGDHFbZsyzbsmTFjCYxYuLB4FUf26dimMQ8ErE&s= >> keKwGEfpcC8MLXAV4QFiAXASguRR9R1dsGMZdyUmd2E&e=>> >>>> >>>> >>>> Thanks, >>>> Sorabh
