Yongzhi Chen created HIVE-11604:
-----------------------------------
Summary: HIVE return wrong results in some queries with PTF
function
Key: HIVE-11604
URL: https://issues.apache.org/jira/browse/HIVE-11604
Project: Hive
Issue Type: Bug
Components: Logical Optimizer
Affects Versions: 1.1.0, 1.2.0
Reporter: Yongzhi Chen
Assignee: Yongzhi Chen
Following query returns empty result which is not right:
{noformat}
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;
{noformat}
After remove row_number() over (partition by id, fkey) as rnum from query, the
right result returns.
Reproduce:
{noformat}
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 |
+---------+-----------+-----------+--+
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
