You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Jim Green <op...@gmail.com> on 2015/09/02 02:35:25 UTC
Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Hi Team,
Below is the minimum reproduce of wrong results in Hive 0.13:
*1. Create 4 tables*
CREATE EXTERNAL TABLE testjoin1( joincol string );
CREATE EXTERNAL TABLE testjoin2(
anothercol string ,
joincol string);
CREATE EXTERNAL TABLE testjoin3( anothercol string);
CREATE EXTERNAL TABLE testjoin4(
joincol string,
wherecol string ,
wherecol2 string);
*2. Insert sample data *
(Note: Make sure you firstly create the dual table which only contains 1
row)
insert into table testjoin1 select '1' from dual;
insert into table testjoin2 select 'another','1' from dual;
insert into table testjoin3 select 'another' from dual;
insert into table testjoin4 select '1','I_AM_MISSING','201501' from dual;
insert into table testjoin4 select '1','I_Shouldnot_be_in_output','201501'
from
dual;
hive> select * from testjoin1;
OK
1
Time taken: 0.04 seconds, Fetched: 1 row(s)
hive> select * from testjoin2;
OK
another 1
Time taken: 0.039 seconds, Fetched: 1 row(s)
hive> select * from testjoin3;
OK
another
Time taken: 0.038 seconds, Fetched: 1 row(s)
hive> select * from testjoin4;
OK
1 I_AM_MISSING 201501
1 I_Shouldnot_be_in_output 201501
Time taken: 0.04 seconds, Fetched: 2 row(s)
*3. SQL1 is returning wrong results.*
Select testjoin4.* From
testjoin1
JOIN testjoin2
ON (testjoin2.joincol = testjoin1.joincol)
JOIN testjoin3
ON (testjoin3.anothercol= testjoin2.anothercol)
JOIN testjoin4
ON (testjoin4.joincol = testjoin1.joincol AND
testjoin4.wherecol2='201501')
WHERE (testjoin4.wherecol='I_AM_MISSING');
1 I_AM_MISSING 201501
1 I_Shouldnot_be_in_output 201501
Time taken: 21.702 seconds, Fetched: 2 row(s)
*4. SQL2 is returning good result(If we move the both filters to WHERE
clause )*
Select testjoin4.* From
testjoin1
JOIN testjoin2
ON (testjoin2.joincol = testjoin1.joincol)
JOIN testjoin3
ON (testjoin3.anothercol= testjoin2.anothercol)
JOIN testjoin4
ON (testjoin4.joincol = testjoin1.joincol)
WHERE (testjoin4.wherecol='I_AM_MISSING' and testjoin4.wherecol2='201501');
1 I_AM_MISSING 201501
Time taken: 20.393 seconds, Fetched: 1 row(s)
—————
*Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
returning wrong results….*
1 I_AM_MISSING 201501
1 I_AM_MISSING 201501
Time taken: 13.983 seconds, Fetched: 2 row(s)
*Anybody knows any related JIRAs?*
--
Thanks,
www.openkb.info
(Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
Re: Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Posted by Jim Green <op...@gmail.com>.
Thanks Ashutosh.
Actually for this kind of query, if I put the 2 filters in WHERE clause
instead of ON clause, the query result is correct.
Do you suggest we put all filters into WHERE or OR clause? And Why?
On Wed, Sep 2, 2015 at 3:13 PM, Ashutosh Chauhan <ha...@apache.org>
wrote:
> It indeed is. Title of bug is symptom of problem and
> doesn't accurately describe the problem. Bug will be triggered if following
> conditions are met:
>
> If query contains 3 or more joins
> AND
> joins are merged (i.e. tables participating in two of those joins are
> joined on same keys)
> AND
> these merged joins are not consecutive in query
> AND
> there is a filter on one of tables who participated in merged join which
> is in WHERE clause (not as join condition)
> then said filter will be dropped.
>
> Query you posted meets all these criteria. You can avoid this bug if you
> rewrite your query such that it violates one of the requirement (listed
> above) to trigger the bug.
>
> Ashutosh
>
>
> On Wed, Sep 2, 2015 at 10:19 AM, Jim Green <op...@gmail.com> wrote:
>
>> Hi Ashutosh,
>>
>> Is Hive-10841 related? from the title of that jira, it sais “where col is
>> not null”caused the issue; however above reproduce did not have that clause.
>>
>>
>>
>> On Wed, Sep 2, 2015 at 2:24 AM, Ashutosh Chauhan <ha...@apache.org>
>> wrote:
>>
>>> https://issues.apache.org/jira/browse/HIVE-10841
>>>
>>> Thanks,
>>> Ashutosh
>>>
>>> On Tue, Sep 1, 2015 at 6:00 PM, Jim Green <op...@gmail.com> wrote:
>>>
>>>> Seems Hive 1.2 fixed this issue. But not sure what is the JIRA related
>>>> and the possibility to backport this fix into Hive 0.13?
>>>>
>>>>
>>>> On Tue, Sep 1, 2015 at 5:35 PM, Jim Green <op...@gmail.com> wrote:
>>>>
>>>>> Hi Team,
>>>>>
>>>>> Below is the minimum reproduce of wrong results in Hive 0.13:
>>>>>
>>>>> *1. Create 4 tables*
>>>>> CREATE EXTERNAL TABLE testjoin1( joincol string );
>>>>> CREATE EXTERNAL TABLE testjoin2(
>>>>> anothercol string ,
>>>>> joincol string);
>>>>>
>>>>> CREATE EXTERNAL TABLE testjoin3( anothercol string);
>>>>>
>>>>> CREATE EXTERNAL TABLE testjoin4(
>>>>> joincol string,
>>>>> wherecol string ,
>>>>> wherecol2 string);
>>>>>
>>>>> *2. Insert sample data *
>>>>> (Note: Make sure you firstly create the dual table which only contains
>>>>> 1 row)
>>>>>
>>>>> insert into table testjoin1 select '1' from dual;
>>>>> insert into table testjoin2 select 'another','1' from dual;
>>>>> insert into table testjoin3 select 'another' from dual;
>>>>> insert into table testjoin4 select '1','I_AM_MISSING','201501' from
>>>>> dual;
>>>>> insert into table testjoin4 select
>>>>> '1','I_Shouldnot_be_in_output','201501' from
>>>>> dual;
>>>>>
>>>>> hive> select * from testjoin1;
>>>>> OK
>>>>> 1
>>>>> Time taken: 0.04 seconds, Fetched: 1 row(s)
>>>>>
>>>>> hive> select * from testjoin2;
>>>>> OK
>>>>> another 1
>>>>> Time taken: 0.039 seconds, Fetched: 1 row(s)
>>>>>
>>>>> hive> select * from testjoin3;
>>>>> OK
>>>>> another
>>>>> Time taken: 0.038 seconds, Fetched: 1 row(s)
>>>>>
>>>>> hive> select * from testjoin4;
>>>>> OK
>>>>> 1 I_AM_MISSING 201501
>>>>> 1 I_Shouldnot_be_in_output 201501
>>>>> Time taken: 0.04 seconds, Fetched: 2 row(s)
>>>>>
>>>>> *3. SQL1 is returning wrong results.*
>>>>>
>>>>> Select testjoin4.* From
>>>>> testjoin1
>>>>> JOIN testjoin2
>>>>> ON (testjoin2.joincol = testjoin1.joincol)
>>>>> JOIN testjoin3
>>>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>>>> JOIN testjoin4
>>>>> ON (testjoin4.joincol = testjoin1.joincol AND
>>>>> testjoin4.wherecol2='201501')
>>>>> WHERE (testjoin4.wherecol='I_AM_MISSING');
>>>>>
>>>>> 1 I_AM_MISSING 201501
>>>>> 1 I_Shouldnot_be_in_output 201501
>>>>> Time taken: 21.702 seconds, Fetched: 2 row(s)
>>>>>
>>>>>
>>>>> *4. SQL2 is returning good result(If we move the both filters to WHERE
>>>>> clause )*
>>>>>
>>>>> Select testjoin4.* From
>>>>> testjoin1
>>>>> JOIN testjoin2
>>>>> ON (testjoin2.joincol = testjoin1.joincol)
>>>>> JOIN testjoin3
>>>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>>>> JOIN testjoin4
>>>>> ON (testjoin4.joincol = testjoin1.joincol)
>>>>> WHERE (testjoin4.wherecol='I_AM_MISSING' and
>>>>> testjoin4.wherecol2='201501');
>>>>>
>>>>> 1 I_AM_MISSING 201501
>>>>> Time taken: 20.393 seconds, Fetched: 1 row(s)
>>>>> —————
>>>>> *Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
>>>>> returning wrong results….*
>>>>>
>>>>> 1 I_AM_MISSING 201501
>>>>> 1 I_AM_MISSING 201501
>>>>> Time taken: 13.983 seconds, Fetched: 2 row(s)
>>>>>
>>>>> *Anybody knows any related JIRAs?*
>>>>>
>>>>> --
>>>>> Thanks,
>>>>> www.openkb.info
>>>>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Thanks,
>>>> www.openkb.info
>>>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>>>
>>>
>>>
>>
>>
>> --
>> Thanks,
>> www.openkb.info
>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>
>
>
--
Thanks,
www.openkb.info
(Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
Re: Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Posted by Ashutosh Chauhan <ha...@apache.org>.
It indeed is. Title of bug is symptom of problem and
doesn't accurately describe the problem. Bug will be triggered if following
conditions are met:
If query contains 3 or more joins
AND
joins are merged (i.e. tables participating in two of those joins are
joined on same keys)
AND
these merged joins are not consecutive in query
AND
there is a filter on one of tables who participated in merged join which is
in WHERE clause (not as join condition)
then said filter will be dropped.
Query you posted meets all these criteria. You can avoid this bug if you
rewrite your query such that it violates one of the requirement (listed
above) to trigger the bug.
Ashutosh
On Wed, Sep 2, 2015 at 10:19 AM, Jim Green <op...@gmail.com> wrote:
> Hi Ashutosh,
>
> Is Hive-10841 related? from the title of that jira, it sais “where col is
> not null”caused the issue; however above reproduce did not have that clause.
>
>
>
> On Wed, Sep 2, 2015 at 2:24 AM, Ashutosh Chauhan <ha...@apache.org>
> wrote:
>
>> https://issues.apache.org/jira/browse/HIVE-10841
>>
>> Thanks,
>> Ashutosh
>>
>> On Tue, Sep 1, 2015 at 6:00 PM, Jim Green <op...@gmail.com> wrote:
>>
>>> Seems Hive 1.2 fixed this issue. But not sure what is the JIRA related
>>> and the possibility to backport this fix into Hive 0.13?
>>>
>>>
>>> On Tue, Sep 1, 2015 at 5:35 PM, Jim Green <op...@gmail.com> wrote:
>>>
>>>> Hi Team,
>>>>
>>>> Below is the minimum reproduce of wrong results in Hive 0.13:
>>>>
>>>> *1. Create 4 tables*
>>>> CREATE EXTERNAL TABLE testjoin1( joincol string );
>>>> CREATE EXTERNAL TABLE testjoin2(
>>>> anothercol string ,
>>>> joincol string);
>>>>
>>>> CREATE EXTERNAL TABLE testjoin3( anothercol string);
>>>>
>>>> CREATE EXTERNAL TABLE testjoin4(
>>>> joincol string,
>>>> wherecol string ,
>>>> wherecol2 string);
>>>>
>>>> *2. Insert sample data *
>>>> (Note: Make sure you firstly create the dual table which only contains
>>>> 1 row)
>>>>
>>>> insert into table testjoin1 select '1' from dual;
>>>> insert into table testjoin2 select 'another','1' from dual;
>>>> insert into table testjoin3 select 'another' from dual;
>>>> insert into table testjoin4 select '1','I_AM_MISSING','201501' from
>>>> dual;
>>>> insert into table testjoin4 select
>>>> '1','I_Shouldnot_be_in_output','201501' from
>>>> dual;
>>>>
>>>> hive> select * from testjoin1;
>>>> OK
>>>> 1
>>>> Time taken: 0.04 seconds, Fetched: 1 row(s)
>>>>
>>>> hive> select * from testjoin2;
>>>> OK
>>>> another 1
>>>> Time taken: 0.039 seconds, Fetched: 1 row(s)
>>>>
>>>> hive> select * from testjoin3;
>>>> OK
>>>> another
>>>> Time taken: 0.038 seconds, Fetched: 1 row(s)
>>>>
>>>> hive> select * from testjoin4;
>>>> OK
>>>> 1 I_AM_MISSING 201501
>>>> 1 I_Shouldnot_be_in_output 201501
>>>> Time taken: 0.04 seconds, Fetched: 2 row(s)
>>>>
>>>> *3. SQL1 is returning wrong results.*
>>>>
>>>> Select testjoin4.* From
>>>> testjoin1
>>>> JOIN testjoin2
>>>> ON (testjoin2.joincol = testjoin1.joincol)
>>>> JOIN testjoin3
>>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>>> JOIN testjoin4
>>>> ON (testjoin4.joincol = testjoin1.joincol AND
>>>> testjoin4.wherecol2='201501')
>>>> WHERE (testjoin4.wherecol='I_AM_MISSING');
>>>>
>>>> 1 I_AM_MISSING 201501
>>>> 1 I_Shouldnot_be_in_output 201501
>>>> Time taken: 21.702 seconds, Fetched: 2 row(s)
>>>>
>>>>
>>>> *4. SQL2 is returning good result(If we move the both filters to WHERE
>>>> clause )*
>>>>
>>>> Select testjoin4.* From
>>>> testjoin1
>>>> JOIN testjoin2
>>>> ON (testjoin2.joincol = testjoin1.joincol)
>>>> JOIN testjoin3
>>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>>> JOIN testjoin4
>>>> ON (testjoin4.joincol = testjoin1.joincol)
>>>> WHERE (testjoin4.wherecol='I_AM_MISSING' and
>>>> testjoin4.wherecol2='201501');
>>>>
>>>> 1 I_AM_MISSING 201501
>>>> Time taken: 20.393 seconds, Fetched: 1 row(s)
>>>> —————
>>>> *Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
>>>> returning wrong results….*
>>>>
>>>> 1 I_AM_MISSING 201501
>>>> 1 I_AM_MISSING 201501
>>>> Time taken: 13.983 seconds, Fetched: 2 row(s)
>>>>
>>>> *Anybody knows any related JIRAs?*
>>>>
>>>> --
>>>> Thanks,
>>>> www.openkb.info
>>>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>>>
>>>
>>>
>>>
>>> --
>>> Thanks,
>>> www.openkb.info
>>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>>
>>
>>
>
>
> --
> Thanks,
> www.openkb.info
> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>
Re: Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Posted by Jim Green <op...@gmail.com>.
Hi Ashutosh,
Is Hive-10841 related? from the title of that jira, it sais “where col is
not null”caused the issue; however above reproduce did not have that clause.
On Wed, Sep 2, 2015 at 2:24 AM, Ashutosh Chauhan <ha...@apache.org>
wrote:
> https://issues.apache.org/jira/browse/HIVE-10841
>
> Thanks,
> Ashutosh
>
> On Tue, Sep 1, 2015 at 6:00 PM, Jim Green <op...@gmail.com> wrote:
>
>> Seems Hive 1.2 fixed this issue. But not sure what is the JIRA related
>> and the possibility to backport this fix into Hive 0.13?
>>
>>
>> On Tue, Sep 1, 2015 at 5:35 PM, Jim Green <op...@gmail.com> wrote:
>>
>>> Hi Team,
>>>
>>> Below is the minimum reproduce of wrong results in Hive 0.13:
>>>
>>> *1. Create 4 tables*
>>> CREATE EXTERNAL TABLE testjoin1( joincol string );
>>> CREATE EXTERNAL TABLE testjoin2(
>>> anothercol string ,
>>> joincol string);
>>>
>>> CREATE EXTERNAL TABLE testjoin3( anothercol string);
>>>
>>> CREATE EXTERNAL TABLE testjoin4(
>>> joincol string,
>>> wherecol string ,
>>> wherecol2 string);
>>>
>>> *2. Insert sample data *
>>> (Note: Make sure you firstly create the dual table which only contains 1
>>> row)
>>>
>>> insert into table testjoin1 select '1' from dual;
>>> insert into table testjoin2 select 'another','1' from dual;
>>> insert into table testjoin3 select 'another' from dual;
>>> insert into table testjoin4 select '1','I_AM_MISSING','201501' from dual;
>>> insert into table testjoin4 select
>>> '1','I_Shouldnot_be_in_output','201501' from
>>> dual;
>>>
>>> hive> select * from testjoin1;
>>> OK
>>> 1
>>> Time taken: 0.04 seconds, Fetched: 1 row(s)
>>>
>>> hive> select * from testjoin2;
>>> OK
>>> another 1
>>> Time taken: 0.039 seconds, Fetched: 1 row(s)
>>>
>>> hive> select * from testjoin3;
>>> OK
>>> another
>>> Time taken: 0.038 seconds, Fetched: 1 row(s)
>>>
>>> hive> select * from testjoin4;
>>> OK
>>> 1 I_AM_MISSING 201501
>>> 1 I_Shouldnot_be_in_output 201501
>>> Time taken: 0.04 seconds, Fetched: 2 row(s)
>>>
>>> *3. SQL1 is returning wrong results.*
>>>
>>> Select testjoin4.* From
>>> testjoin1
>>> JOIN testjoin2
>>> ON (testjoin2.joincol = testjoin1.joincol)
>>> JOIN testjoin3
>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>> JOIN testjoin4
>>> ON (testjoin4.joincol = testjoin1.joincol AND
>>> testjoin4.wherecol2='201501')
>>> WHERE (testjoin4.wherecol='I_AM_MISSING');
>>>
>>> 1 I_AM_MISSING 201501
>>> 1 I_Shouldnot_be_in_output 201501
>>> Time taken: 21.702 seconds, Fetched: 2 row(s)
>>>
>>>
>>> *4. SQL2 is returning good result(If we move the both filters to WHERE
>>> clause )*
>>>
>>> Select testjoin4.* From
>>> testjoin1
>>> JOIN testjoin2
>>> ON (testjoin2.joincol = testjoin1.joincol)
>>> JOIN testjoin3
>>> ON (testjoin3.anothercol= testjoin2.anothercol)
>>> JOIN testjoin4
>>> ON (testjoin4.joincol = testjoin1.joincol)
>>> WHERE (testjoin4.wherecol='I_AM_MISSING' and
>>> testjoin4.wherecol2='201501');
>>>
>>> 1 I_AM_MISSING 201501
>>> Time taken: 20.393 seconds, Fetched: 1 row(s)
>>> —————
>>> *Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
>>> returning wrong results….*
>>>
>>> 1 I_AM_MISSING 201501
>>> 1 I_AM_MISSING 201501
>>> Time taken: 13.983 seconds, Fetched: 2 row(s)
>>>
>>> *Anybody knows any related JIRAs?*
>>>
>>> --
>>> Thanks,
>>> www.openkb.info
>>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>>
>>
>>
>>
>> --
>> Thanks,
>> www.openkb.info
>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>
>
>
--
Thanks,
www.openkb.info
(Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
Re: Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Posted by Ashutosh Chauhan <ha...@apache.org>.
https://issues.apache.org/jira/browse/HIVE-10841
Thanks,
Ashutosh
On Tue, Sep 1, 2015 at 6:00 PM, Jim Green <op...@gmail.com> wrote:
> Seems Hive 1.2 fixed this issue. But not sure what is the JIRA related and
> the possibility to backport this fix into Hive 0.13?
>
>
> On Tue, Sep 1, 2015 at 5:35 PM, Jim Green <op...@gmail.com> wrote:
>
>> Hi Team,
>>
>> Below is the minimum reproduce of wrong results in Hive 0.13:
>>
>> *1. Create 4 tables*
>> CREATE EXTERNAL TABLE testjoin1( joincol string );
>> CREATE EXTERNAL TABLE testjoin2(
>> anothercol string ,
>> joincol string);
>>
>> CREATE EXTERNAL TABLE testjoin3( anothercol string);
>>
>> CREATE EXTERNAL TABLE testjoin4(
>> joincol string,
>> wherecol string ,
>> wherecol2 string);
>>
>> *2. Insert sample data *
>> (Note: Make sure you firstly create the dual table which only contains 1
>> row)
>>
>> insert into table testjoin1 select '1' from dual;
>> insert into table testjoin2 select 'another','1' from dual;
>> insert into table testjoin3 select 'another' from dual;
>> insert into table testjoin4 select '1','I_AM_MISSING','201501' from dual;
>> insert into table testjoin4 select
>> '1','I_Shouldnot_be_in_output','201501' from
>> dual;
>>
>> hive> select * from testjoin1;
>> OK
>> 1
>> Time taken: 0.04 seconds, Fetched: 1 row(s)
>>
>> hive> select * from testjoin2;
>> OK
>> another 1
>> Time taken: 0.039 seconds, Fetched: 1 row(s)
>>
>> hive> select * from testjoin3;
>> OK
>> another
>> Time taken: 0.038 seconds, Fetched: 1 row(s)
>>
>> hive> select * from testjoin4;
>> OK
>> 1 I_AM_MISSING 201501
>> 1 I_Shouldnot_be_in_output 201501
>> Time taken: 0.04 seconds, Fetched: 2 row(s)
>>
>> *3. SQL1 is returning wrong results.*
>>
>> Select testjoin4.* From
>> testjoin1
>> JOIN testjoin2
>> ON (testjoin2.joincol = testjoin1.joincol)
>> JOIN testjoin3
>> ON (testjoin3.anothercol= testjoin2.anothercol)
>> JOIN testjoin4
>> ON (testjoin4.joincol = testjoin1.joincol AND
>> testjoin4.wherecol2='201501')
>> WHERE (testjoin4.wherecol='I_AM_MISSING');
>>
>> 1 I_AM_MISSING 201501
>> 1 I_Shouldnot_be_in_output 201501
>> Time taken: 21.702 seconds, Fetched: 2 row(s)
>>
>>
>> *4. SQL2 is returning good result(If we move the both filters to WHERE
>> clause )*
>>
>> Select testjoin4.* From
>> testjoin1
>> JOIN testjoin2
>> ON (testjoin2.joincol = testjoin1.joincol)
>> JOIN testjoin3
>> ON (testjoin3.anothercol= testjoin2.anothercol)
>> JOIN testjoin4
>> ON (testjoin4.joincol = testjoin1.joincol)
>> WHERE (testjoin4.wherecol='I_AM_MISSING' and
>> testjoin4.wherecol2='201501');
>>
>> 1 I_AM_MISSING 201501
>> Time taken: 20.393 seconds, Fetched: 1 row(s)
>> —————
>> *Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
>> returning wrong results….*
>>
>> 1 I_AM_MISSING 201501
>> 1 I_AM_MISSING 201501
>> Time taken: 13.983 seconds, Fetched: 2 row(s)
>>
>> *Anybody knows any related JIRAs?*
>>
>> --
>> Thanks,
>> www.openkb.info
>> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>>
>
>
>
> --
> Thanks,
> www.openkb.info
> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>
Re: Wrong results from join query in Hive 0.13 and also 1.0 with reproduce.
Posted by Jim Green <op...@gmail.com>.
Seems Hive 1.2 fixed this issue. But not sure what is the JIRA related and
the possibility to backport this fix into Hive 0.13?
On Tue, Sep 1, 2015 at 5:35 PM, Jim Green <op...@gmail.com> wrote:
> Hi Team,
>
> Below is the minimum reproduce of wrong results in Hive 0.13:
>
> *1. Create 4 tables*
> CREATE EXTERNAL TABLE testjoin1( joincol string );
> CREATE EXTERNAL TABLE testjoin2(
> anothercol string ,
> joincol string);
>
> CREATE EXTERNAL TABLE testjoin3( anothercol string);
>
> CREATE EXTERNAL TABLE testjoin4(
> joincol string,
> wherecol string ,
> wherecol2 string);
>
> *2. Insert sample data *
> (Note: Make sure you firstly create the dual table which only contains 1
> row)
>
> insert into table testjoin1 select '1' from dual;
> insert into table testjoin2 select 'another','1' from dual;
> insert into table testjoin3 select 'another' from dual;
> insert into table testjoin4 select '1','I_AM_MISSING','201501' from dual;
> insert into table testjoin4 select '1','I_Shouldnot_be_in_output','201501'
> from
> dual;
>
> hive> select * from testjoin1;
> OK
> 1
> Time taken: 0.04 seconds, Fetched: 1 row(s)
>
> hive> select * from testjoin2;
> OK
> another 1
> Time taken: 0.039 seconds, Fetched: 1 row(s)
>
> hive> select * from testjoin3;
> OK
> another
> Time taken: 0.038 seconds, Fetched: 1 row(s)
>
> hive> select * from testjoin4;
> OK
> 1 I_AM_MISSING 201501
> 1 I_Shouldnot_be_in_output 201501
> Time taken: 0.04 seconds, Fetched: 2 row(s)
>
> *3. SQL1 is returning wrong results.*
>
> Select testjoin4.* From
> testjoin1
> JOIN testjoin2
> ON (testjoin2.joincol = testjoin1.joincol)
> JOIN testjoin3
> ON (testjoin3.anothercol= testjoin2.anothercol)
> JOIN testjoin4
> ON (testjoin4.joincol = testjoin1.joincol AND
> testjoin4.wherecol2='201501')
> WHERE (testjoin4.wherecol='I_AM_MISSING');
>
> 1 I_AM_MISSING 201501
> 1 I_Shouldnot_be_in_output 201501
> Time taken: 21.702 seconds, Fetched: 2 row(s)
>
>
> *4. SQL2 is returning good result(If we move the both filters to WHERE
> clause )*
>
> Select testjoin4.* From
> testjoin1
> JOIN testjoin2
> ON (testjoin2.joincol = testjoin1.joincol)
> JOIN testjoin3
> ON (testjoin3.anothercol= testjoin2.anothercol)
> JOIN testjoin4
> ON (testjoin4.joincol = testjoin1.joincol)
> WHERE (testjoin4.wherecol='I_AM_MISSING' and testjoin4.wherecol2='201501');
>
> 1 I_AM_MISSING 201501
> Time taken: 20.393 seconds, Fetched: 1 row(s)
> —————
> *Another test is done in Hive 1.0 and found both SQL1 and SQL2 are
> returning wrong results….*
>
> 1 I_AM_MISSING 201501
> 1 I_AM_MISSING 201501
> Time taken: 13.983 seconds, Fetched: 2 row(s)
>
> *Anybody knows any related JIRAs?*
>
> --
> Thanks,
> www.openkb.info
> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
>
--
Thanks,
www.openkb.info
(Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)