You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "@Sanjiv Singh" <sa...@gmail.com> on 2016/01/09 11:12:44 UTC
Column data mismatch after join !!!
Hi All,
I am facing strange behaviour as explained below. I have tow hive table T1
and T2 , joined with LEFT OUTER JOIN ..I am getting strange value for two
columns t2c2 t2c3 of table T2 after join.
See below complete detail :
*Table T1 :*
create table T1 ( t1c1 int , t1c2 int , t1c3 int ) clustered by (t1c1) into
2 buckets stored as orc TBLPROPERTIES('transactional'='true');
4 4 1
4 4 0
1 1 1
1 1 0
5 5 1
*Table T2: *
create table T2 ( t2c0 int , t2c1 int , t2c2 int , t2c3 int ) clustered by
( t2c1) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
;
0 1 -1 3
0 1 0 0
0 4 6 6
0 4 1 6
1 1 0 2
1 4 3 5
1 4 2 5
*Query : *
*select *from T1 aLEFT OUTER JOIN T2 bON a.t1c2 = b.t2c1;*
*Result set : *(not expected )
a.t1c1 a.t1c2 a.t1c3 b.t2c0 b.t2c1 b.t2c2 b.t2c3
4 4 1 0 4
4 6
4 4 1 0 4 4
1
4 4 1 1 4
4 3
4 4 1 1 4
4 2
4 4 0 0 4
4 6
4 4 0 0 4 4
1
4 4 0 1 4 4
3
4 4 0 1 4
4 2
1 1 1 0 1 1
-1
1 1 1 0 1 1
0
1 1 1 1 1 1
0
1 1 0 0 1
1 -1
1 1 0 0 1 1
0
1 1 0 1 1
1 0
5 5 1 NULL NULL NULL NULL
Error description : values in result set b.t2c2 and b.t2c3 are strange
and not expected . -1 in b.t2c3 is no more belong to T2.t2c3 , and 4 in
b.t2c2 is no more belong to T2.t2c2.
I am not sure whats wrong with. Please help me to identify the issue and
resolve it.
Expected result :
1 1 1 0 1 -1 3
1 1 1 1 1 0 2
1 1 1 0 1 0 0
1 1 0 0 1 -1 3
1 1 0 1 1 0 2
1 1 0 0 1 0 0
4 4 1 1 4 3 5
4 4 1 0 4 6 6
4 4 1 1 4 2 5
4 4 1 0 4 1 6
4 4 0 1 4 3 5
4 4 0 0 4 6 6
4 4 0 1 4 2 5
4 4 0 0 4 1 6
5 5 1 <null> <null> <null> <null>
Regards
Sanjiv Singh
Mob : +091 9990-447-339
Re: Column data mismatch after join !!!
Posted by "@Sanjiv Singh" <sa...@gmail.com>.
My case is very opposite to below JIRAs as in my case , using subquery
giving correct result ...but using original table giving mismatched column
value.
https://issues.apache.org/jira/browse/HIVE-9613
https://issues.apache.org/jira/browse/HIVE-9327
Regards
Sanjiv Singh
Mob : +091 9990-447-339
On Mon, Jan 11, 2016 at 10:44 PM, David Morel <da...@amakuru.net>
wrote:
> On 11 Jan 2016, at 8:54, @Sanjiv Singh wrote:
>
> > Adding to that ....
> >
> > Surprisingly it is giving correct result if i use derived tables rather
> > than original tables;
>
> Related to https://issues.apache.org/jira/browse/HIVE-9613 ?
>
> David
>
Re: Column data mismatch after join !!!
Posted by David Morel <da...@amakuru.net>.
On 11 Jan 2016, at 8:54, @Sanjiv Singh wrote:
> Adding to that ....
>
> Surprisingly it is giving correct result if i use derived tables rather
> than original tables;
Related to https://issues.apache.org/jira/browse/HIVE-9613 ?
David
Re: Column data mismatch after join !!!
Posted by "@Sanjiv Singh" <sa...@gmail.com>.
Adding to that ....
Surprisingly it is giving correct result if i use derived tables rather
than original tables;
changes query :
*select *from ( select * from T1 ) aLEFT OUTER JOIN ( select * from T2) bON
a.t1c2 = b.t2c1;*
Regards
Sanjiv Singh
Mob : +091 9990-447-339
On Mon, Jan 11, 2016 at 10:50 AM, @Sanjiv Singh <sa...@gmail.com>
wrote:
> Any help on this ?
>
>
> Regards
> Sanjiv Singh
> Mob : +091 9990-447-339
>
> On Sat, Jan 9, 2016 at 3:42 PM, @Sanjiv Singh <sa...@gmail.com>
> wrote:
>
>> Hi All,
>>
>> I am facing strange behaviour as explained below. I have tow hive table
>> T1 and T2 , joined with LEFT OUTER JOIN ..I am getting strange value for
>> two columns t2c2 t2c3 of table T2 after join.
>>
>> See below complete detail :
>>
>> *Table T1 :*
>> create table T1 ( t1c1 int , t1c2 int , t1c3 int ) clustered by (t1c1)
>> into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
>> 4 4 1
>> 4 4 0
>> 1 1 1
>> 1 1 0
>> 5 5 1
>>
>> *Table T2: *
>> create table T2 ( t2c0 int , t2c1 int , t2c2 int , t2c3 int ) clustered
>> by ( t2c1) into 2 buckets stored as orc
>> TBLPROPERTIES('transactional'='true'); ;
>> 0 1 -1 3
>> 0 1 0 0
>> 0 4 6 6
>> 0 4 1 6
>> 1 1 0 2
>> 1 4 3 5
>> 1 4 2 5
>>
>> *Query : *
>>
>>
>>
>>
>>
>> *select *from T1 aLEFT OUTER JOIN T2 bON a.t1c2 = b.t2c1;*
>>
>> *Result set : *(not expected )
>> a.t1c1 a.t1c2 a.t1c3 b.t2c0 b.t2c1 b.t2c2 b.t2c3
>> 4 4 1 0 4
>> 4 6
>> 4 4 1 0 4 4
>> 1
>> 4 4 1 1 4
>> 4 3
>> 4 4 1 1 4
>> 4 2
>> 4 4 0 0 4
>> 4 6
>> 4 4 0 0 4 4
>> 1
>> 4 4 0 1 4 4
>> 3
>> 4 4 0 1 4
>> 4 2
>> 1 1 1 0 1 1
>> -1
>> 1 1 1 0 1 1
>> 0
>> 1 1 1 1 1 1
>> 0
>> 1 1 0 0 1
>> 1 -1
>> 1 1 0 0 1 1
>> 0
>> 1 1 0 1 1
>> 1 0
>> 5 5 1 NULL NULL NULL NULL
>>
>> Error description : values in result set b.t2c2 and b.t2c3 are
>> strange and not expected . -1 in b.t2c3 is no more belong to T2.t2c3 ,
>> and 4 in b.t2c2 is no more belong to T2.t2c2.
>> I am not sure whats wrong with. Please help me to identify the issue and
>> resolve it.
>>
>>
>>
>> Expected result :
>>
>> 1 1 1 0 1 -1 3
>> 1 1 1 1 1 0 2
>> 1 1 1 0 1 0 0
>> 1 1 0 0 1 -1 3
>> 1 1 0 1 1 0 2
>> 1 1 0 0 1 0 0
>> 4 4 1 1 4 3 5
>> 4 4 1 0 4 6 6
>> 4 4 1 1 4 2 5
>> 4 4 1 0 4 1 6
>> 4 4 0 1 4 3 5
>> 4 4 0 0 4 6 6
>> 4 4 0 1 4 2 5
>> 4 4 0 0 4 1 6
>> 5 5 1 <null> <null> <null> <null>
>>
>>
>> Regards
>> Sanjiv Singh
>> Mob : +091 9990-447-339
>>
>
>
Re: Column data mismatch after join !!!
Posted by "@Sanjiv Singh" <sa...@gmail.com>.
Any help on this ?
Regards
Sanjiv Singh
Mob : +091 9990-447-339
On Sat, Jan 9, 2016 at 3:42 PM, @Sanjiv Singh <sa...@gmail.com>
wrote:
> Hi All,
>
> I am facing strange behaviour as explained below. I have tow hive table
> T1 and T2 , joined with LEFT OUTER JOIN ..I am getting strange value for
> two columns t2c2 t2c3 of table T2 after join.
>
> See below complete detail :
>
> *Table T1 :*
> create table T1 ( t1c1 int , t1c2 int , t1c3 int ) clustered by (t1c1)
> into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
> 4 4 1
> 4 4 0
> 1 1 1
> 1 1 0
> 5 5 1
>
> *Table T2: *
> create table T2 ( t2c0 int , t2c1 int , t2c2 int , t2c3 int ) clustered
> by ( t2c1) into 2 buckets stored as orc
> TBLPROPERTIES('transactional'='true'); ;
> 0 1 -1 3
> 0 1 0 0
> 0 4 6 6
> 0 4 1 6
> 1 1 0 2
> 1 4 3 5
> 1 4 2 5
>
> *Query : *
>
>
>
>
>
> *select *from T1 aLEFT OUTER JOIN T2 bON a.t1c2 = b.t2c1;*
>
> *Result set : *(not expected )
> a.t1c1 a.t1c2 a.t1c3 b.t2c0 b.t2c1 b.t2c2 b.t2c3
> 4 4 1 0 4
> 4 6
> 4 4 1 0 4 4
> 1
> 4 4 1 1 4
> 4 3
> 4 4 1 1 4
> 4 2
> 4 4 0 0 4
> 4 6
> 4 4 0 0 4 4
> 1
> 4 4 0 1 4 4
> 3
> 4 4 0 1 4
> 4 2
> 1 1 1 0 1 1
> -1
> 1 1 1 0 1 1
> 0
> 1 1 1 1 1 1
> 0
> 1 1 0 0 1
> 1 -1
> 1 1 0 0 1 1
> 0
> 1 1 0 1 1
> 1 0
> 5 5 1 NULL NULL NULL NULL
>
> Error description : values in result set b.t2c2 and b.t2c3 are
> strange and not expected . -1 in b.t2c3 is no more belong to T2.t2c3 ,
> and 4 in b.t2c2 is no more belong to T2.t2c2.
> I am not sure whats wrong with. Please help me to identify the issue and
> resolve it.
>
>
>
> Expected result :
>
> 1 1 1 0 1 -1 3
> 1 1 1 1 1 0 2
> 1 1 1 0 1 0 0
> 1 1 0 0 1 -1 3
> 1 1 0 1 1 0 2
> 1 1 0 0 1 0 0
> 4 4 1 1 4 3 5
> 4 4 1 0 4 6 6
> 4 4 1 1 4 2 5
> 4 4 1 0 4 1 6
> 4 4 0 1 4 3 5
> 4 4 0 0 4 6 6
> 4 4 0 1 4 2 5
> 4 4 0 0 4 1 6
> 5 5 1 <null> <null> <null> <null>
>
>
> Regards
> Sanjiv Singh
> Mob : +091 9990-447-339
>