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 <sanjiv.is...@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 <sanjiv.is...@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
>>
>
>
