[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lefty Leverenz updated HIVE-15544:
--
Labels: TODOC2.2 sub-query (was: sub-query)
> 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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ashutosh Chauhan updated HIVE-15544:
Resolution: Fixed
Fix Version/s: 2.2.0
Status: Resolved (was: Patch Available)
Pushed to master. Thanks [~vgarg]
> 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: 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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Patch Available (was: Open)
> 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: sub-query
> 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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Open (was: Patch Available)
> 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: sub-query
> 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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Attachment: HIVE-15544.5.patch
Addressed review comments.
> 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: sub-query
> 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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Patch Available (was: Open)
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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.patch, HIVE-15544.4.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Attachment: HIVE-15544.4.patch
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.patch, HIVE-15544.4.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Open (was: Patch Available)
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Patch Available (was: Open)
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Attachment: HIVE-15544.3.patch
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Open (was: Patch Available)
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.patch,
> HIVE-15544.3.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Patch Available (was: Open)
Latest patch contains check to ensure that scalar subquery generates atmost 1
value. The way it does is plan is generated with count() on top of subquery
which is further passed on to a UDF (sq_count_check) which throws an exception
if count > 1.
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Attachment: HIVE-15544.2.patch
> 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: sub-query
> Attachments: HIVE-15544.1.patch, HIVE-15544.2.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Open (was: Patch Available)
> 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: sub-query
> Attachments: HIVE-15544.1.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Status: Patch Available (was: Open)
This first patch is a preliminary patch which has support for scalar subqueries
in WHERE/HAVING.
This patch is missing the following known stuff:
* q tests
* Check to ensure that subquery expression is producing exactly one row
* Check to disallow various subquery restrictions (this patch currently
disables those check since those doesn't work with scalar queries)
> 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: sub-query
> Attachments: HIVE-15544.1.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)
[jira] [Updated] (HIVE-15544) Support scalar subqueries
[
https://issues.apache.org/jira/browse/HIVE-15544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated HIVE-15544:
---
Attachment: HIVE-15544.1.patch
> 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: sub-query
> Attachments: HIVE-15544.1.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)
