You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "ransom.hezhiqiang (JIRA)" <ji...@apache.org> on 2013/04/16 11:11:16 UTC
[jira] [Updated] (HIVE-4365) wrong result in left semi join
[ https://issues.apache.org/jira/browse/HIVE-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ransom.hezhiqiang updated HIVE-4365:
------------------------------------
Description:
wrong result in left semi join while hive.optimize.ppd=true
for example:
1、create table
create table t1(c1 int,c2 int, c3 int, c4 int, c5 double,c6 int,c7 string) row format DELIMITED FIELDS TERMINATED BY '|';
create table t2(c1 int) ;
2、load data
load data local inpath '/home/test/t1.txt' OVERWRITE into table t1;
load data local inpath '/home/test/t2.txt' OVERWRITE into table t2;
t1 data:
1|3|10003|52|781.96|555|201203
1|3|10003|39|782.96|555|201203
1|3|10003|87|783.96|555|201203
2|5|10004|24|789.96|555|201203
2|5|10004|58|788.96|555|201203
t2 data:
555
3、excute Query
select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 and t1.c1 = '1' and t1.c7 = '201203' ;
can got result.
select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 where t1.c1 = '1' and t1.c7 = '201203' ;
can't got result.
was:
wrong result in left semi join while hive.optimize.ppd=true
for example:
1、create table
create table t1(c1 int,c2 int, c3 int, c4 int, c5 double,c6 int,c7 string) row format DELIMITED FIELDS TERMINATED BY '|';
create table t2(c1 int) ;
2、load data
load data local inpath '/home/omm/t1.txt' OVERWRITE into table t1;
load data local inpath '/home/omm/t2.txt' OVERWRITE into table t2;
t1 data:
1|3|10003|52|781.96|555|201203
1|3|10003|39|782.96|555|201203
1|3|10003|87|783.96|555|201203
2|5|10004|24|789.96|555|201203
2|5|10004|58|788.96|555|201203
t2 data:
555
3、excute Query
select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 and t1.c1 = '1' and t1.c7 = '201203' ;
can got result.
select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 where t1.c1 = '1' and t1.c7 = '201203' ;
can't got result.
> wrong result in left semi join
> ------------------------------
>
> Key: HIVE-4365
> URL: https://issues.apache.org/jira/browse/HIVE-4365
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.9.0, 0.10.0
> Reporter: ransom.hezhiqiang
>
> wrong result in left semi join while hive.optimize.ppd=true
> for example:
> 1、create table
> create table t1(c1 int,c2 int, c3 int, c4 int, c5 double,c6 int,c7 string) row format DELIMITED FIELDS TERMINATED BY '|';
> create table t2(c1 int) ;
> 2、load data
> load data local inpath '/home/test/t1.txt' OVERWRITE into table t1;
> load data local inpath '/home/test/t2.txt' OVERWRITE into table t2;
> t1 data:
> 1|3|10003|52|781.96|555|201203
> 1|3|10003|39|782.96|555|201203
> 1|3|10003|87|783.96|555|201203
> 2|5|10004|24|789.96|555|201203
> 2|5|10004|58|788.96|555|201203
> t2 data:
> 555
> 3、excute Query
> select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 and t1.c1 = '1' and t1.c7 = '201203' ;
> can got result.
> select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5,t1.c6,t1.c7 from t1 left semi join t2 on t1.c6 = t2.c1 where t1.c1 = '1' and t1.c7 = '201203' ;
> can't got result.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira