[jira] [Updated] (HIVE-11604) HIVE return wrong results in some queries with PTF function
[ https://issues.apache.org/jira/browse/HIVE-11604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sergey Shelukhin updated HIVE-11604: Should this issue be backported to branch-1? It looks like a bug. > 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.2.0, 1.1.0, 2.0.0 >Reporter: Yongzhi Chen >Assignee: Yongzhi Chen > Fix For: 2.0.0 > > Attachments: HIVE-11604.1.patch, HIVE-11604.2.patch > > > 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:1> 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:1> from ( > 0: jdbc:hive2://localhost:1> select id, fkey > 0: jdbc:hive2://localhost:1> from tlb1 group by id, fkey > 0: jdbc:hive2://localhost:1> ) ddd > 0: jdbc:hive2://localhost:1> > 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)
[jira] [Updated] (HIVE-11604) HIVE return wrong results in some queries with PTF function
[ https://issues.apache.org/jira/browse/HIVE-11604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sergey Shelukhin updated HIVE-11604: Target Version/s: 1.3.0 > 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.2.0, 1.1.0, 2.0.0 >Reporter: Yongzhi Chen >Assignee: Yongzhi Chen > Fix For: 2.0.0 > > Attachments: HIVE-11604.1.patch, HIVE-11604.2.patch > > > 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:1> 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:1> from ( > 0: jdbc:hive2://localhost:1> select id, fkey > 0: jdbc:hive2://localhost:1> from tlb1 group by id, fkey > 0: jdbc:hive2://localhost:1> ) ddd > 0: jdbc:hive2://localhost:1> > 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)
[jira] [Updated] (HIVE-11604) HIVE return wrong results in some queries with PTF function
[ https://issues.apache.org/jira/browse/HIVE-11604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Yongzhi Chen updated HIVE-11604: Attachment: HIVE-11604.1.patch > 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.2.0, 1.1.0 >Reporter: Yongzhi Chen >Assignee: Yongzhi Chen > Attachments: HIVE-11604.1.patch > > > 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:1> 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:1> from ( > 0: jdbc:hive2://localhost:1> select id, fkey > 0: jdbc:hive2://localhost:1> from tlb1 group by id, fkey > 0: jdbc:hive2://localhost:1> ) ddd > 0: jdbc:hive2://localhost:1> > 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)
[jira] [Updated] (HIVE-11604) HIVE return wrong results in some queries with PTF function
[ https://issues.apache.org/jira/browse/HIVE-11604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Yongzhi Chen updated HIVE-11604: Affects Version/s: 2.0.0 > 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.2.0, 1.1.0, 2.0.0 >Reporter: Yongzhi Chen >Assignee: Yongzhi Chen > Attachments: HIVE-11604.1.patch > > > 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:1> 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:1> from ( > 0: jdbc:hive2://localhost:1> select id, fkey > 0: jdbc:hive2://localhost:1> from tlb1 group by id, fkey > 0: jdbc:hive2://localhost:1> ) ddd > 0: jdbc:hive2://localhost:1> > 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)
[jira] [Updated] (HIVE-11604) HIVE return wrong results in some queries with PTF function
[ https://issues.apache.org/jira/browse/HIVE-11604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Yongzhi Chen updated HIVE-11604: Attachment: HIVE-11604.2.patch > 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.2.0, 1.1.0, 2.0.0 >Reporter: Yongzhi Chen >Assignee: Yongzhi Chen > Attachments: HIVE-11604.1.patch, HIVE-11604.2.patch > > > 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:1> 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:1> from ( > 0: jdbc:hive2://localhost:1> select id, fkey > 0: jdbc:hive2://localhost:1> from tlb1 group by id, fkey > 0: jdbc:hive2://localhost:1> ) ddd > 0: jdbc:hive2://localhost:1> > 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)