[jira] [Comment Edited] (IMPALA-891) Add support for INTERSECT and EXCEPT set operations

["David J (Jira)" <ji...@apache.org>]

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

David J edited comment on IMPALA-891 at 6/9/20, 1:12 AM:
---------------------------------------------------------

[~tarmstrong] I really appreciate you taking a crack at it. Unfortunately I don't think those solves will fit my use case. I was banging my head against the wall trying to get it to work with a Cross Join or an Exist today. This was as good as I could get for the moment, but isn't scalable.
{code:java}
  Select t1.id1, g1.g, g2.g
  from t1
  Inner Join t2 as g1 on g1.id2 = t1.id2
    Where g IN (x,y,z)
  Inner Join t2 as g2 on g2.id2 = g1.id2
    Where g ilike '%a%'{code}
This would be the dream
{code:java}
  Select t1.id1, t2.g
  from t1
  Inner Join(
    Select g, id2 from t2
    Where g IN (x,y,z)
    Intersect
    Select g, id2 from t2
    Where g like '%a%'
   ) t2 ON t2.id2 = t1.id2{code}


was (Author: davidj314):
I really appreciate you taking a crack at it. Unfortunately I don't think those solves will fit my use case. I was banging my head against the wall trying to get it to work with a Cross Join or an Exist today. This was as good as I could get for the moment, but isn't scalable.
{code:java}
  Select t1.id1, g1.g, g2.g
  from t1
  Inner Join t2 as g1 on g1.id2 = t1.id2
    Where g IN (x,y,z)
  Inner Join t2 as g2 on g2.id2 = g1.id2
    Where g ilike '%a%'{code}
This would be the dream
{code:java}
  Select t1.id1, t2.g
  from t1
  Inner Join(
    Select g, id2 from t2
    Where g IN (x,y,z)
    Intersect
    Select g, id2 from t2
    Where g like '%a%'
   ) t2 ON t2.id2 = t1.id2{code}

> Add support for INTERSECT and EXCEPT set operations
> ---------------------------------------------------
>
>                 Key: IMPALA-891
>                 URL: https://issues.apache.org/jira/browse/IMPALA-891
>             Project: IMPALA
>          Issue Type: New Feature
>          Components: Backend
>    Affects Versions: Impala 1.2.4, Impala 2.5.0, Impala 2.6.0, Impala 2.7.0
>            Reporter: Jonathan Seidman
>            Priority: Major
>              Labels: sql-language, tpc-ds, usability
>
> Set functionality includes the below.  Today, Impala has just {{UNION}} & {{UNION ALL}}.
> {code}
> UNION [DISTINCT]
> UNION ALL
> INTERSECT [DISTINCT]
> INTERSECT ALL
> EXCEPT [DISTINCT]
> EXCEPT ALL
> * MINUS is an alias for EXCEPT
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org