hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gopal V (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-14997) Hive query left join get wrong result
Date Tue, 18 Oct 2016 17:46:58 GMT

    [ https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15586126#comment-15586126
] 

Gopal V commented on HIVE-14997:
--------------------------------

Looks like a duplicate of HIVE-14027

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

Mime
View raw message