[jira] [Commented] (HIVE-14997) Hive query left join get wrong result
[
https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15587322#comment-15587322
]
lios.li commented on HIVE-14997:
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.ida.name a.l_dateb.idb.class b.score b.l_date_c1
_c2
4 musk2016-10-18 NULLNULLNULLNULLtruetrue
> 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 math98
> 2 math96
> 3 math94
> 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.idb.class b.score
> 4 muskNULLNULLNULL
> so, i think there is a bug.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14997) Hive query left join get wrong result
[
https://issues.apache.org/jira/browse/HIVE-14997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15587324#comment-15587324
]
lios.li commented on HIVE-14997:
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.ida.name a.l_dateb.idb.class b.score b.l_date_c1
_c2
4 musk2016-10-18 NULLNULLNULLNULLtruetrue
> 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 math98
> 2 math96
> 3 math94
> 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.idb.class b.score
> 4 muskNULLNULLNULL
> so, i think there is a bug.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14997) Hive query left join get wrong result
[
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 math98
> 2 math96
> 3 math94
> 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.idb.class b.score
> 4 muskNULLNULLNULL
> so, i think there is a bug.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
