[HIVE-11931] Join sql cannot get result

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.1.1, 1.2.1
• Component/s: Hive, Query Planning, Query Processor, SQL
• Assignee: Xiaomeng Huang
• Reporter: NING DING
• Created: 23/Sep/15 06:22
• Updated: 10/Oct/15 01:16
• Resolved: 10/Oct/15 01:16

Description

I found a join issue in hive-1.2.1 and hive-1.1.1.
The create table sql is as below.

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;

Please put attached sample data file 000000_0 in /tmp/join_case folder.
Then load data.

LOAD DATA LOCAL INPATH '/tmp/join_case/000000_0' OVERWRITE INTO TABLE join_case;

Run the following sql, but cannot get searching result.

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;

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.

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;

Also, the following sql can get results in hive-1.2.1 and hive-1.1.1.

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; 

Anyone can take a look at this issue?
Thanks.

Issue Links

• duplicates: HIVE-10996 Aggregation / Projection over Multi-Join Inner Query producing incorrect results