[ 
https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

lios.li updated HIVE-14997:
---------------------------
    Assignee:     (was: 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
>
> 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)

Reply via email to