hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "@Sanjiv Singh" <sanjiv.is...@gmail.com>
Subject Re: Column data mismatch after join !!!
Date Mon, 11 Jan 2016 07:54:31 GMT
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.on@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.on@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
>>
>
>

Mime
View raw message