[jira] [Commented] (DRILL-4232) Support for EXCEPT set operator

[benj (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DRILL-4232?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16820990#comment-16820990
 ] 

benj commented on DRILL-4232:
-----------------------------

Of course, It's also possible to simulate *EXCEPT ALL*
{code:java}
SELECT a FROM ....`file1.csvh`
EXCEPT ALL
SELECT a FROM ....`file2.csvh`
{code}
Becomes
{code:java}
SELECT a FROM
(
 SELECT a, idfile
 , row_number() OVER (PARTITION BY a, idfile) AS rn
 , count(CASE WHEN idfile = 1 THEN 1 END) OVER (PARTITION BY a) AS nb_elt_tbl1
 , count(CASE WHEN idfile = 0 THEN 1 END) OVER (PARTITION BY a) AS nb_elt_tbl2
 FROM
 (
  SELECT a, 1 idfile FROM ....`file1.csvh` AS tbl1
  UNION ALL
  SELECT a, 0 idfile FROM ....`file2.csvh` AS tbl2
 ) AS lvl_union
) AS lvl_over
WHERE idfile = 1 AND rn <= nb_elt_tbl1 - nb_elt_tbl2
{code}

> Support for EXCEPT set operator
> -------------------------------
>
>                 Key: DRILL-4232
>                 URL: https://issues.apache.org/jira/browse/DRILL-4232
>             Project: Apache Drill
>          Issue Type: New Feature
>          Components: Query Planning &amp; Optimization
>            Reporter: Victoria Markman
>            Priority: Major
>




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)