You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Sorabh Hamirwasia <so...@gmail.com> on 2018/01/25 22:56:23 UTC
LATERAL and UNNEST support for Drill
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://issues.apache.org/jira/browse/DRILL-5999>
Design Document
<https://docs.google.com/document/d/1-RCIJ0F7VwAqOxkVB305zADwtX-OS43Qj2kUmIILUaQ/edit?usp=sharing>
Thanks,
Sorabh
Re: LATERAL and UNNEST support for Drill
Posted by Julian Hyde <jh...@apache.org>.
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 <am...@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 <jhyde@apache.org <ma...@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 <cs...@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 <sh...@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 <te...@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.apache@gmail.com<ma...@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
Re: LATERAL and UNNEST support for Drill
Posted by Aman Sinha <am...@apache.org>.
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[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 <cs...@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 <sh...@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 <te...@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.apache@gmail.com<ma...@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
> >>
> >
>
>
Re: LATERAL and UNNEST support for Drill
Posted by Julian Hyde <jh...@apache.org>.
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 <cs...@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 <sh...@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 <te...@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 <so...@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
>>
>
Re: LATERAL and UNNEST support for Drill
Posted by Chunhui Shi <cs...@mapr.com>.
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 <sh...@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 <te...@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 <so...@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
>
Re: LATERAL and UNNEST support for Drill
Posted by Julian Hyde <jh...@apache.org>.
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://issues.apache.org/jira/browse/CALCITE-1472 <https://issues.apache.org/jira/browse/CALCITE-1472>
> On Jan 29, 2018, at 8:58 AM, Sorabh Hamirwasia <sh...@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://aka.ms/o0ukef>
> ________________________________
> From: Ted Dunning <te...@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 <so...@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://issues.apache.org/jira/browse/DRILL-5999<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://docs.google.com/document/d/1-RCIJ0F7VwAqOxkVB305zADwtX-OS43Qj2kUmIILUaQ/edit?usp=sharing<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
>
Re: LATERAL and UNNEST support for Drill
Posted by Sorabh Hamirwasia <sh...@mapr.com>.
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://aka.ms/o0ukef>
________________________________
From: Ted Dunning <te...@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 <so...@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://issues.apache.org/jira/browse/DRILL-5999<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://docs.google.com/document/d/1-RCIJ0F7VwAqOxkVB305zADwtX-OS43Qj2kUmIILUaQ/edit?usp=sharing<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
Re: LATERAL and UNNEST support for Drill
Posted by Ted Dunning <te...@gmail.com>.
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 <so...@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://issues.apache.org/jira/browse/DRILL-5999>
>
> Design Document
> <https://docs.google.com/document/d/1-RCIJ0F7VwAqOxkVB305zAD
> wtX-OS43Qj2kUmIILUaQ/edit?usp=sharing>
>
>
> Thanks,
> Sorabh
>