hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aihua Xu (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-9228) Problem with subquery using windowing functions
Date Wed, 14 Jan 2015 17:33:34 GMT

     [ https://issues.apache.org/jira/browse/HIVE-9228?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Aihua Xu updated HIVE-9228:
---------------------------
    Description: 
The following query with window functions failed. The internal query works fine.

select col1, col2, col3 from (select col1,col2, col3, count(case when col4=1 then 1 end )
over (partition by col1, col2) as col5, row_number() over (partition by col1, col2 order by
col4) as col6 from tab1) t;

HIVE generates an execution plan with 2 jobs. 
1. The first job is to basically calculate window function for col5.  
2. The second job is to calculate window function for col6 and output.

The plan says the first job outputs the columns col1, col2, col3, col4 

  was:
The following query with window functions failed. The internal query works fine.

select st_fips_cd, zip_cd_5, hh_surr_key
from
(
select st_fips_cd, zip_cd_5, hh_surr_key,
count( case when advtg_len_rsdnc_cd = '1' then 1 end ) over (partition by st_fips_cd, zip_cd_5)
as CNT_ADVTG_LEN_RSDNC_CD_1,
row_number() over (partition by st_fips_cd, zip_cd_5 order by hh_surr_key asc) as analytic_row_number3
from hh_agg
where analytic_row_number2 = 1
) t;


> Problem with subquery using windowing functions
> -----------------------------------------------
>
>                 Key: HIVE-9228
>                 URL: https://issues.apache.org/jira/browse/HIVE-9228
>             Project: Hive
>          Issue Type: Bug
>          Components: PTF-Windowing
>    Affects Versions: 0.13.1
>            Reporter: Aihua Xu
>            Assignee: Aihua Xu
>         Attachments: create_table_tab1.sql, tab1.csv
>
>   Original Estimate: 96h
>  Remaining Estimate: 96h
>
> The following query with window functions failed. The internal query works fine.
> select col1, col2, col3 from (select col1,col2, col3, count(case when col4=1 then 1 end
) over (partition by col1, col2) as col5, row_number() over (partition by col1, col2 order
by col4) as col6 from tab1) t;
> HIVE generates an execution plan with 2 jobs. 
> 1. The first job is to basically calculate window function for col5.  
> 2. The second job is to calculate window function for col6 and output.
> The plan says the first job outputs the columns col1, col2, col3, col4 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message