[jira] [Issue Comment Deleted] (HIVE-14997) Hive query left join get wrong result

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

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

lios.li updated HIVE-14997:
---------------------------
    Comment: was deleted

(was: select *,isnull(b.id),isnull(b.class) 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 try the problem in HIVE-14027:

select *,isnull(b.id),isnull(b.class) 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 get the right result :
a.id	a.name	a.l_date	b.id	b.class	b.score	b.l_date	_c1	_c2
4	musk	2016-10-18	NULL	NULL	NULL	NULL	true	true

)

> 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)