[HIVE-11604] HIVE return wrong results in some queries with PTF function

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.1.0, 1.2.0, 2.0.0
• Fix Version/s: 2.0.0
• Component/s: Logical Optimizer
• Target Version/s: 1.3.0
• Assignee: Yongzhi Chen
• Reporter: Yongzhi Chen
• Created: 19/Aug/15 13:40
• Updated: 27/Feb/24 22:23
• Resolved: 02/Sep/15 18:50

Description

Following query returns empty result which is not right:

select ddd.id, ddd.fkey, aaa.name
from (
    select id, fkey, 
    row_number() over (partition by id, fkey) as rnum
    from tlb1 group by id, fkey
 ) ddd 
inner join tlb2 aaa on aaa.fid = ddd.fkey;

After remove row_number() over (partition by id, fkey) as rnum from query, the right result returns.

Reproduce:

create table tlb1 (id int, fkey int, val string);
create table tlb2 (fid int, name string);
insert into table tlb1 values(100,1,'abc');
insert into table tlb1 values(200,1,'efg');
insert into table tlb2 values(1, 'key1');

select ddd.id, ddd.fkey, aaa.name
from (
    select id, fkey, 
    row_number() over (partition by id, fkey) as rnum
    from tlb1 group by id, fkey
 ) ddd 
inner join tlb2 aaa on aaa.fid = ddd.fkey;
....
INFO  : Ended Job = job_local1070163923_0017
+---------+-----------+-----------+--+
No rows selected (14.248 seconds)
| ddd.id  | ddd.fkey  | aaa.name  |
+---------+-----------+-----------+--+
+---------+-----------+-----------+--+

0: jdbc:hive2://localhost:10000> select ddd.id, ddd.fkey, aaa.name
from (
    select id, fkey 
    from tlb1 group by id, fkey
 ) ddd 
inner join tlb2 aaa on aaa.fid = ddd.fkey;select ddd.id, ddd.fkey, aaa.name
0: jdbc:hive2://localhost:10000> from (
0: jdbc:hive2://localhost:10000>     select id, fkey 
0: jdbc:hive2://localhost:10000>     from tlb1 group by id, fkey
0: jdbc:hive2://localhost:10000>  ) ddd 
0: jdbc:hive2://localhost:10000> 
inner join tlb2 aaa on aaa.fid = ddd.fkey;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
...
INFO  : Ended Job = job_local672340505_0019
+---------+-----------+-----------+--+
2 rows selected (14.383 seconds)
| ddd.id  | ddd.fkey  | aaa.name  |
+---------+-----------+-----------+--+
| 100     | 1         | key1      |
| 200     | 1         | key1      |
+---------+-----------+-----------+--+