Hi all,
Any one could kindly help me to take a look on this issue?
Please find details in https://issues.apache.org/jira/browse/HIVE-11931
Recently, 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;
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;
Thanks.
