You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by itisha Gupta <it...@gmail.com> on 2020/01/15 08:42:58 UTC
Regarding EXCEPT clause of drill
Hey team,
I am trying to use EXCEPT operator of Apache drill but it's not working.
Does Apache drill supports EXCEPT?
Regards,
Itisha
Re: Regarding EXCEPT clause of drill
Posted by Ted Dunning <te...@gmail.com>.
I think that set subtraction is considerably harder than union. It is
unlikely to be a simple hack to an operator.
But it should be possible to build planner rules that expand an except
expression into a plan to do an outer join and filter away results that
have non-null right hand sides.
The real problem is that virtually nobody even knows that except exists.
On Thu, Jan 16, 2020 at 10:57 AM Paul Rogers <pa...@yahoo.com.invalid>
wrote:
> Thanks Arina.
>
> I believe that EXCEPT is set subtraction A - B. Drill does (I believe)
> already support UNION distinct, which is set union (A U B). If so, it would
> be a nice little project for someone to implement EXCEPT by reversing the
> polarity of comparison: discard the non-dups rather than the dups. I'm sure
> it is a bit harder than that, but still would be a good project for someone
> who want to get experience with query engine internals.
>
> Thanks,
> - Paul
>
>
>
> On Thursday, January 16, 2020, 04:16:49 AM PST, Arina Yelchiyeva <
> arina.yelchiyeva@gmail.com> wrote:
>
> Paul is right, Drill does not support Except syntax.
> In this Jira in comments you can find ways how to rewrite the query to
> achieve the same result: https://issues.apache.org/jira/browse/DRILL-4232
> <https://issues.apache.org/jira/browse/DRILL-4232>
>
> Kind regards,
> Arina
>
> > On Jan 16, 2020, at 1:09 AM, Paul Rogers <pa...@yahoo.com.INVALID>
> wrote:
> >
> > Hi Itisha,
> >
> > Welcome to the Drill user mail list!
> >
> > For those readers (like me) who are not familiar with the keyword,
> EXCEPT works like UNION to join to SELECT statements: SELECT ... EXCEPT
> SELECT ... See [1]. Seems pretty useful.
> >
> > A quick check of our parser source code suggests that Drill does not
> support the EXCEPT syntax. Drill does not even treat EXCEPT as a keyword.
> >
> > Please file a JIRA ticket to request this feature.
> >
> > Thanks,
> > - Paul
> >
> >
> > [1] https://www.tutorialspoint.com/sql/sql-except-clause.htm
> >
> >
> >
> > On Wednesday, January 15, 2020, 12:50:36 AM PST, itisha Gupta <
> itishagupta.2396@gmail.com> wrote:
> >
> > Hey team,
> >
> > I am trying to use EXCEPT operator of Apache drill but it's not working.
> > Does Apache drill supports EXCEPT?
> >
> > Regards,
> > Itisha
>
Re: Regarding EXCEPT clause of drill
Posted by Paul Rogers <pa...@yahoo.com.INVALID>.
Thanks Arina.
I believe that EXCEPT is set subtraction A - B. Drill does (I believe) already support UNION distinct, which is set union (A U B). If so, it would be a nice little project for someone to implement EXCEPT by reversing the polarity of comparison: discard the non-dups rather than the dups. I'm sure it is a bit harder than that, but still would be a good project for someone who want to get experience with query engine internals.
Thanks,
- Paul
On Thursday, January 16, 2020, 04:16:49 AM PST, Arina Yelchiyeva <ar...@gmail.com> wrote:
Paul is right, Drill does not support Except syntax.
In this Jira in comments you can find ways how to rewrite the query to achieve the same result: https://issues.apache.org/jira/browse/DRILL-4232 <https://issues.apache.org/jira/browse/DRILL-4232>
Kind regards,
Arina
> On Jan 16, 2020, at 1:09 AM, Paul Rogers <pa...@yahoo.com.INVALID> wrote:
>
> Hi Itisha,
>
> Welcome to the Drill user mail list!
>
> For those readers (like me) who are not familiar with the keyword, EXCEPT works like UNION to join to SELECT statements: SELECT ... EXCEPT SELECT ... See [1]. Seems pretty useful.
>
> A quick check of our parser source code suggests that Drill does not support the EXCEPT syntax. Drill does not even treat EXCEPT as a keyword.
>
> Please file a JIRA ticket to request this feature.
>
> Thanks,
> - Paul
>
>
> [1] https://www.tutorialspoint.com/sql/sql-except-clause.htm
>
>
>
> On Wednesday, January 15, 2020, 12:50:36 AM PST, itisha Gupta <it...@gmail.com> wrote:
>
> Hey team,
>
> I am trying to use EXCEPT operator of Apache drill but it's not working.
> Does Apache drill supports EXCEPT?
>
> Regards,
> Itisha
Re: Regarding EXCEPT clause of drill
Posted by Arina Yelchiyeva <ar...@gmail.com>.
Paul is right, Drill does not support Except syntax.
In this Jira in comments you can find ways how to rewrite the query to achieve the same result: https://issues.apache.org/jira/browse/DRILL-4232 <https://issues.apache.org/jira/browse/DRILL-4232>
Kind regards,
Arina
> On Jan 16, 2020, at 1:09 AM, Paul Rogers <pa...@yahoo.com.INVALID> wrote:
>
> Hi Itisha,
>
> Welcome to the Drill user mail list!
>
> For those readers (like me) who are not familiar with the keyword, EXCEPT works like UNION to join to SELECT statements: SELECT ... EXCEPT SELECT ... See [1]. Seems pretty useful.
>
> A quick check of our parser source code suggests that Drill does not support the EXCEPT syntax. Drill does not even treat EXCEPT as a keyword.
>
> Please file a JIRA ticket to request this feature.
>
> Thanks,
> - Paul
>
>
> [1] https://www.tutorialspoint.com/sql/sql-except-clause.htm
>
>
>
> On Wednesday, January 15, 2020, 12:50:36 AM PST, itisha Gupta <it...@gmail.com> wrote:
>
> Hey team,
>
> I am trying to use EXCEPT operator of Apache drill but it's not working.
> Does Apache drill supports EXCEPT?
>
> Regards,
> Itisha
Re: Regarding EXCEPT clause of drill
Posted by Paul Rogers <pa...@yahoo.com.INVALID>.
Hi Itisha,
Welcome to the Drill user mail list!
For those readers (like me) who are not familiar with the keyword, EXCEPT works like UNION to join to SELECT statements: SELECT ... EXCEPT SELECT ... See [1]. Seems pretty useful.
A quick check of our parser source code suggests that Drill does not support the EXCEPT syntax. Drill does not even treat EXCEPT as a keyword.
Please file a JIRA ticket to request this feature.
Thanks,
- Paul
[1] https://www.tutorialspoint.com/sql/sql-except-clause.htm
On Wednesday, January 15, 2020, 12:50:36 AM PST, itisha Gupta <it...@gmail.com> wrote:
Hey team,
I am trying to use EXCEPT operator of Apache drill but it's not working.
Does Apache drill supports EXCEPT?
Regards,
Itisha