[jira] [Created] (HIVE-14997) Hive query left join get wrong result

["lios.li (JIRA)" <ji...@apache.org>]

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)