[ https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15587324#comment-15587324 ]
lios.li edited comment on HIVE-14997 at 10/19/16 1:34 AM: ---------------------------------------------------------- i try the problem in HIVE-14027: select *,isnull(b.id),isnull(b.class) from test.student_info a left join test.student_score b on a.id=b.id where (b.id='' or b.id is null); and get the right result : a.id a.name a.l_date b.id b.class b.score b.l_date _c1 _c2 4 musk 2016-10-18 NULL NULL NULL NULL true true was (Author: lios): select *,isnull(b.id),isnull(b.class) from test.student_info a left join test.student_score b on a.id=b.id where (b.id='' or b.id is null) and trim(a.id)='4'; i try the problem in HIVE-14027: select *,isnull(b.id),isnull(b.class) from test.student_info a left join test.student_score b on a.id=b.id where (b.id='' or b.id is null); and get the right result : a.id a.name a.l_date b.id b.class b.score b.l_date _c1 _c2 4 musk 2016-10-18 NULL NULL NULL NULL true true > Hive query left join get wrong result > ------------------------------------- > > Key: HIVE-14997 > URL: https://issues.apache.org/jira/browse/HIVE-14997 > Project: Hive > Issue Type: Bug > Components: SQL > Affects Versions: 0.14.0 > Environment: Hive 0.14.0 > Subversion > file:///Users/ghagleitner/Projects/hive-svn/rel-prep/hive-14-rel-prep -r > Unknown > Compiled by ghagleitner on Sat Nov 8 23:25:06 PST 2014 > From source with checksum 49c2182a0856f7917f571802a7594b00 > Reporter: lios.li > > First, create two tables. > CREATE DATABASE IF NOT EXISTS test; > USE test; > DROP TABLE IF EXISTS student_info; > CREATE TABLE IF NOT EXISTS student_info( > id string COMMENT 'student id', > name string COMMENT 'student name' > ) > PARTITIONED BY (l_date string) > ROW FORMAT DELIMITED > FIELDS TERMINATED BY '\t'; > ALTER TABLE test.student_info SET SERDEPROPERTIES('serialization.null.format' > = ''); > DROP TABLE IF EXISTS student_score; > CREATE TABLE IF NOT EXISTS student_score( > id string COMMENT 'student id', > class string COMMENT 'class', > score int COMMENT 'class score' > ) > PARTITIONED BY (l_date string) > ROW FORMAT DELIMITED > FIELDS TERMINATED BY '\t'; > ALTER TABLE test.student_score SET > SERDEPROPERTIES('serialization.null.format' = ''); > 4 records in table student_info, > 1 jobs > 2 cook > 3 gates > 4 musk > 3 records in table student_score, > 1 math 98 > 2 math 96 > 3 math 94 > I want get the student who has no score and id is '4'. > select * from test.student_info a > left join test.student_score b > on a.id=b.id > where (b.id='' or b.id is null) > and a.id='4'; > and i got nothing. > but, i add the 'trim()'. > select * from test.student_info a > left join test.student_score b > on a.id=b.id > where (b.id='' or b.id is null) > and trim(a.id)='4'; > i can get what i want. > a.id a.name b.id b.class b.score > 4 musk NULL NULL NULL > so, i think there is a bug. -- This message was sent by Atlassian JIRA (v6.3.4#6332)