hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Srini Pindi (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-11034) Joining multiple tables producing different results with different order of join
Date Wed, 17 Jun 2015 18:30:04 GMT

     [ https://issues.apache.org/jira/browse/HIVE-11034?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Srini Pindi updated HIVE-11034:
-------------------------------
    Attachment:     (was: steps_to_reproduce_.docx)

> Joining multiple tables producing different results with different order of join
> --------------------------------------------------------------------------------
>
>                 Key: HIVE-11034
>                 URL: https://issues.apache.org/jira/browse/HIVE-11034
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Processor
>    Affects Versions: 0.13.0
>         Environment: Linux 2.6.32-279.19.1.el6.x86_64
>            Reporter: Srini Pindi
>            Priority: Critical
>
> Join between one main table with other tables with different join columns returns wrong
results in hive. Changing the order of the joins between main table and other tables is producing
different results.
> Here are the steps to reproduce the issue:
> 1. Create tables as follows:
>     create table p(ck string, email string);
>     create table a1(ck string, flag string);
>     create table a2(email string, flag string);
>     create table a3(ck string, flag string);
> 2. Load data into the tables as follows:
>     P
>     ||ck||email||
>     |10|e10|
>     |20|e20|
>     |30|e30|
>     |40|e40|
>  
>     A1
>     ||ck||flag||
>     |10||N|
>     |20||Y|
>     |30||Y|
>     |40||Y|
>     A2
>     ||email||flag||
>     |e10||Y|
>     |e20||N|
>     |e30||Y|
>     |e40||Y|
>  
>     A3
>     ||ck||flag||
>     |10||Y|
>     |20||Y|
>     |30||N|
>     |40||Y|
>   
>  3. Good query:
> {panel}
> select p.ck 
> from p 
> left outer join a1 on p.ck = a1.ck 
> left outer join a3 on p.ck = a3.ck 
> left outer join a2 on p.email = a2.email 
> where a1.flag = 'Y'
>   and a3.flag = 'Y'
>   and a2.flag = 'Y'
> ;
> {panel}
> and results are
>   40
> 4. Bad query
> {panel}
> select p.ck 
> from p 
> left outer join a1 on p.ck = a1.ck 
> left outer join a2 on p.email = a2.email 
> left outer join a3 on p.ck = a3.ck 
> where a1.flag = 'Y'
>   and a2.flag = 'Y'
>   and a3.flag = 'Y'
> ;
> {panel}
>  Producing results as:
>  30
>  40



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message