hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "NING DING (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-11931) Join sql cannot get result
Date Wed, 23 Sep 2015 06:34:04 GMT

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

NING DING updated HIVE-11931:
-----------------------------
    Attachment: 000000_0

sample data file

> Join sql cannot get result
> --------------------------
>
>                 Key: HIVE-11931
>                 URL: https://issues.apache.org/jira/browse/HIVE-11931
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive, Query Planning, Query Processor, SQL
>    Affects Versions: 1.1.1, 1.2.1
>            Reporter: NING DING
>         Attachments: 000000_0
>
>
> I found a join issue in hive-1.2.1 and hive-1.1.1.
> The create table sql is as below.
> {code}
> CREATE TABLE IF NOT EXISTS join_case(
>     orderid  bigint,
>     tradeitemid bigint,
>     id bigint
> ) ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ',' 
> LINES TERMINATED BY '\n'
> STORED AS TEXTFILE;
> {code}
> Please put attached sample data file 000000_0 in /tmp/join_case folder.
> Then load data.
> {code}
> LOAD DATA LOCAL INPATH '/tmp/join_case/000000_0' OVERWRITE INTO TABLE join_case;
> {code}
> Run the following sql, but cannot get searching result.
> {code}
> select a.id from 
> (
> select orderid as orderid, max(id) as id from join_case group by orderid
> ) a 
> join 
> (
> select id as id , orderid as orderid from join_case
> ) b
> on a.id = b.id limit 10;
> {code}
> This issue also occurs in hive-1.1.0-cdh5.4.5.
> But in apache hive-1.0.1 the above sql can return 10 rows.
> After exchanging the sequence of "orderid as orderid" and "max(id) as id", the following
sql can get result in hive-1.2.1 and hive-1.1.1.
> {code}
> select a.id from 
> (
> select max(id) as id, orderid as orderid from join_case group by orderid
> ) a 
> join 
> (
> select id as id , orderid as orderid from join_case
> ) b
> on a.id = b.id limit 10;
> {code}
> Also, the following sql can get results in hive-1.2.1 and hive-1.1.1.
> {code}
> select a.id from 
> (
> select orderid as orderid, id as id from join_case group by orderid, id
> ) a 
> join 
> (
> select id as id , orderid as orderid from join_case
> ) b
> on a.id = b.id limit 10; 
> {code}
> Anyone can take a look at this issue? 
> Thanks.



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

Mime
View raw message