[HIVE-14997] Hive query left join get wrong result

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.14.0
• Component/s: SQL

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

• Flags: Important
• Assignee: Unassigned
• Reporter: lios.li
• Created: 18/Oct/16 09:25
• Updated: 19/Oct/16 01:34

Description

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.