lios.li created HIVE-14997:
------------------------------
Summary: 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)
