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> 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. > > 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 > >  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: email@example.com > > 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. 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 > > > > > >  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: firstname.lastname@example.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 > >> > > > >