Description
Currently HIVE only support IN/EXISTS/NOT IN/NOT EXISTS subqueries. HIVE doesn't allow sub-queries such as:
explain select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
,date_dim d
,item i
where a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_sold_date_sk = d.d_date_sk
and s.ss_item_sk = i.i_item_sk
and d.d_month_seq =
(select distinct (d_month_seq)
from date_dim
where d_year = 2000
and d_moy = 2 )
and i.i_current_price > 1.2 *
(select avg(j.i_current_price)
from item j
where j.i_category = i.i_category)
group by a.ca_state
having count(*) >= 10
order by cnt
limit 100;
We initially plan to support such scalar subqueries in filter i.e. WHERE and HAVING
Attachments
Attachments
Issue Links
- is related to
-
HIVE-15721 Allow IN/NOT IN correlated subquery with aggregates
-
- Closed
-
-
HIVE-16793 Scalar sub-query: sq_count_check not required if gby keys are constant
-
- Closed
-
- links to
