[ 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)