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
