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
>
