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> > 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=9Y08i3YgrresMOxi7InbjxT0WSHQkcPjJufQWLI9PGk&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=9Y08i3YgrresMOxi7InbjxT0WSHQkcPjJufQWLI9PGk&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 >> >
