You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Tasselmi (Jira)" <ji...@apache.org> on 2020/08/14 10:55:00 UTC
[jira] [Commented] (HIVE-19458) Wrong result for LEFT JOIN when
hive.auto.convert.join.use.nonstaged set to true
[ https://issues.apache.org/jira/browse/HIVE-19458?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17177696#comment-17177696 ]
Tasselmi commented on HIVE-19458:
---------------------------------
I got the wrong results too.
when I `set hive.auto.convert.join=true;`
mapjoin got wrong result.
`select * from a left join b on a.id = b.id where b.id is null `
I got zero record . But I am sure there should be some records. when I set hive.auto.convert.join to be false, I got the right results.
> Wrong result for LEFT JOIN when hive.auto.convert.join.use.nonstaged set to true
> --------------------------------------------------------------------------------
>
> Key: HIVE-19458
> URL: https://issues.apache.org/jira/browse/HIVE-19458
> Project: Hive
> Issue Type: Bug
> Affects Versions: 2.1.0
> Reporter: Ruochen Zou
> Priority: Critical
>
> {code:java}
> create table if not exists left_join_l (
> id int,
> name string
> );
> create table if not exists left_join_r (
> id int,
> age int
> );
> insert into left_join_l VALUES (1, 'Li'), (2, 'Smith'), (3, 'Johnson');
> insert into left_join_r VALUES (1, 20), (2, 30);
> set hive.auto.convert.join=true;
> set hive.auto.convert.join.use.nonstaged=true;
> set hive.cbo.enable=false;
> select a.id aid, b.id bid, name, age from
> left_join_l a
> left join left_join_r b
> on (a.id=b.id)
> {code}
> The returned is:
> ||aid||bid||name||age||
> |1|1|Li|1|
> |2|2|Smith|2|
> |3|NULL|Johnson|NULL|
> Expect:
> ||aid||bid||name||age||
> |1|1|Li|20|
> |2|2|Smith|30|
> |3|NULL|Johnson|NULL|
> Another query:
> {code:java}
> set hive.auto.convert.join=true;
> set hive.auto.convert.join.use.nonstaged=true;
> set hive.cbo.enable=false;
> select a.id aid, b.id bid, name, age from
> left_join_r a
> left join left_join_l b
> on (a.id=b.id)
> {code}
> it will fails with this message:
> {code:java}
> Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row {"id":1,"age":20}
> at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:169)
> at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)
> at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:430)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:342)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:167)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:415)
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1616)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
> Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row {"id":1,"age":20}
> at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:499)
> at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:160)
> ... 8 more
> Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unexpected exception from MapJoinOperator : org.apache.hadoop.io.IntWritable cannot be cast to org.apache.hadoop.io.Text
> at org.apache.hadoop.hive.ql.exec.MapJoinOperator.process(MapJoinOperator.java:454)
> at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:878)
> at org.apache.hadoop.hive.ql.exec.TableScanOperator.process(TableScanOperator.java:130)
> at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:149)
> at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:489)
> ... 9 more
> Caused by: java.lang.ClassCastException: org.apache.hadoop.io.IntWritable cannot be cast to org.apache.hadoop.io.Text
> at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableStringObjectInspector.getPrimitiveWritableObject(WritableStringObjectInspector.java:41)
> at org.apache.hadoop.hive.serde2.lazy.LazyUtils.writePrimitiveUTF8(LazyUtils.java:259)
> at org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.serialize(LazySimpleSerDe.java:292)
> at org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.serializeField(LazySimpleSerDe.java:247)
> at org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.doSerialize(LazySimpleSerDe.java:231)
> at org.apache.hadoop.hive.serde2.AbstractEncodingAwareSerDe.serialize(AbstractEncodingAwareSerDe.java:55)
> at org.apache.hadoop.hive.ql.exec.FileSinkOperator.process(FileSinkOperator.java:719)
> at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:878)
> at org.apache.hadoop.hive.ql.exec.SelectOperator.process(SelectOperator.java:95)
> at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:878)
> at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.internalForward(CommonJoinOperator.java:647)
> at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genAllOneUniqueJoinObject(CommonJoinOperator.java:679)
> at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:757)
> at org.apache.hadoop.hive.ql.exec.MapJoinOperator.process(MapJoinOperator.java:441)
> ... 13 more
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)