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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> > 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 < > [email protected]> 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 >
