You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Tim Armstrong (Jira)" <ji...@apache.org> on 2020/07/08 21:31:02 UTC
[jira] [Updated] (IMPALA-5226) Add support for arbitrary subqueries
in WHERE/HAVING, including inside disjuncts
[ https://issues.apache.org/jira/browse/IMPALA-5226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tim Armstrong updated IMPALA-5226:
----------------------------------
Summary: Add support for arbitrary subqueries in WHERE/HAVING, including inside disjuncts (was: Add support for disjunction of subqueries)
> Add support for arbitrary subqueries in WHERE/HAVING, including inside disjuncts
> --------------------------------------------------------------------------------
>
> Key: IMPALA-5226
> URL: https://issues.apache.org/jira/browse/IMPALA-5226
> Project: IMPALA
> Issue Type: New Feature
> Components: Frontend
> Reporter: Greg Rahn
> Priority: Major
> Labels: planner, ramp-up, sql-language, tpc-ds
>
> The following query will error with:
> ERROR: AnalysisException: Subqueries in OR predicates are not supported:
> {noformat}
> select
> c_customer_sk,
> c_first_name,
> c_last_name
> from customer
> where
> (exists (select *
> from web_sales, date_dim
> where c_customer_sk = ws_bill_customer_sk and
> ws_sold_date_sk = d_date_sk and
> d_date = '2002-01-01')
> or
> exists (select *
> from catalog_sales, date_dim
> where c_customer_sk = cs_ship_customer_sk and
> cs_sold_date_sk = d_date_sk and
> d_date = '2002-01-01')
> )
> order by 1;
> {noformat}
> When there is a disjunction of subqueries, it can be transformed into a union all and a semi-join like such which is able to be run in impala today.
> {noformat}
> with
> v1 as (
> select
> ws_bill_customer_sk as customer_sk
> from web_sales,
> date_dim
> where ws_sold_date_sk = d_date_sk
> and d_date = '2002-01-01'
> union all
> select
> cs_ship_customer_sk as customer_sk
> from catalog_sales,
> date_dim
> where cs_sold_date_sk = d_date_sk
> and d_date = '2002-01-01'
> )
> select
> c_customer_sk,
> c_first_name,
> c_last_name
> from customer
> left semi join v1 on (customer_sk = c_customer_sk)
> order by 1
> {noformat}
--
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