[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15829209#comment-15829209
]
Lefty Leverenz edited comment on HIVE-15544 at 1/26/17 11:11 AM:
-----------------------------------------------------------------
Latest patch contains rewrites for correlated scalar subqueries with aggregates
and more tests.
Note that this patch also disables the following:
* IN/NOT IN correlated subqueries containing aggregates (HIVE checks for such
queries and throw an exception)
* SCALAR correlated subqueries containing aggregates with non-equi join
predicates on correlated columns (HIVE throws an exception for such queries)
Above restrictions will need to be documented.
Edit (1/26/2017): HIVE-15721 removes the IN/NOT IN restriction, so it doesn't
need to be documented. (Sorry about using an edit, but JIRA comments changed
from threaded to flat so now there's no reply option in the interface.) -- Lefty
was (Author: vgarg):
Latest patch contains rewrites for correlated scalar subqueries with aggregates
and more tests.
Note that this patch also disables the following:
* IN/NOT IN correlated subqueries containing aggregates (HIVE checks for such
queries and throw an exception)
* SCALAR correlated subqueries containing aggregates with non-equi join
predicates on correlated columns (HIVE throws an exception for such queries)
Above restrictions will need to be documented.
> Support scalar subqueries
> -------------------------
>
> Key: HIVE-15544
> URL: https://issues.apache.org/jira/browse/HIVE-15544
> Project: Hive
> Issue Type: Sub-task
> Components: SQL
> Reporter: Vineet Garg
> Assignee: Vineet Garg
> Labels: TODOC2.2, sub-query
> Fix For: 2.2.0
>
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.patch, HIVE-15544.4.patch, HIVE-15544.5.patch
>
>
> Currently HIVE only support IN/EXISTS/NOT IN/NOT EXISTS subqueries. HIVE
> doesn't allow sub-queries such as:
> {code}
> 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;
> {code}
> We initially plan to support such scalar subqueries in filter i.e. WHERE and
> HAVING
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
