Mailing-List: contact issues-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Thu, 31 Dec 2015 06:09:49 +0000 (UTC)
From: "mingren (JIRA)" <jira@apache.org>
To: issues@hive.apache.org
Message-ID: <JIRA.12924819.1451461297000.32840.1451542189781@Atlassian.JIRA>
In-Reply-To: <JIRA.12924819.1451461297000@Atlassian.JIRA>
References: <JIRA.12924819.1451461297000@Atlassian.JIRA>
 <JIRA.12924819.1451461297383@arcas>
Subject: [jira] [Commented] (HIVE-12760) union all in hive returns incorrect
 results.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/HIVE-12760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15075754#comment-15075754 ] 

mingren commented on HIVE-12760:
--------------------------------

Thanks for reply! I have tried to test on the lastest version which did work right. 

Is there a way to make a patch for Hive 1.2.1 version,because upgrading code can't be easily done.

> union all in hive returns incorrect results.
> --------------------------------------------
>
>                 Key: HIVE-12760
>                 URL: https://issues.apache.org/jira/browse/HIVE-12760
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 1.2.1
>         Environment: Hadoop 2.7.1
> Hive 1.2.1
>            Reporter: mingren
>
> The issue can be recreated with following steps
> 1.create table
> {quote}
> CREATE TABLE `union_case`(
>   `shopid` bigint,
>   `platform` string,
>   `source` string,
>   `pv` bigint,
>   `uv` bigint,
>   `pv_all` bigint,
>   `uv_all` bigint,
>   `pv_rate` float,
>   `uv_rate` float 
> )
> PARTITIONED BY (
>   `visit_date` string)
> ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
> STORED AS INPUTFORMAT
>   'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
> OUTPUTFORMAT
>   'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
> LOCATION
>   'hdfs://mgjcluster/apps/hive/warehouse/union_case'
> {quote}
> 2.insert the following data
> {quote}
> insert into table union_case partition(visit_date='2015-12-24') values 
> (102448,'wap','t1',2,1,10,10,0.5,0.5),
> (102448,'pc','t1',2,1,10,10,0.5,0.5),
> (102448,'app','t1',2,1,10,10,0.5,0.5),
> (102443,'wap','t2',2,1,10,10,0.5,0.5),
> (102443,'pc','t2',2,1,10,10,0.5,0.5),
> (102443,'app','t2',2,1,10,10,0.5,0.5)
> {quote}
> 3.execute the following query
> {quote}
> select platform,count(1) from
> (select 
>       shopid,
>       1 as platform,
>       source,
>       pv,
>       uv,
>       pv_all,
>       uv_all,
>       pv_rate,
>       uv_rate
>   from union_case
>   where visit_date = '2015-12-24' and platform = 'pc'
>   union all
>   select 
>       a.shopid,
>       2 as platform,
>       a.source,
>       a.pv as pv,
>       a.uv as uv,
>       b.pv_all as pv_all,
>       b.uv_all as uv_all,
>       a.pv/b.pv_all as pv_rate,
>       a.uv/b.uv_all as uv_rate
>   from (
>       select a.shopid,a.source,sum(a.pv) as pv,sum(a.uv) as uv 
>       from union_case a 
>       where a.visit_date = '2015-12-24' and platform in ('wap','app') 
>       group by a.shopid, a.source) a 
>   left join (
>       select 
>       shopid,
>       sum(pv) as pv_all,
>       sum(uv) as uv_all
>     from union_case
>     where visit_date = '2015-12-24' and platform in ('wap','app')
>     group by shopid
>   ) b 
>   on a.shopid = b.shopid
>   ) x
> group by platform
> limit 5
> {quote}
> 4. output of quering
> {quote}
> NULL    2
> 2   2
> {quote}
> **obviously, result is wrong.**


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