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 >
