[
https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
lios.li reassigned HIVE-14997:
------------------------------
Assignee: lios.li
> 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
> Assignee: 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)
