hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thejas M Nair (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
Date Thu, 23 Jun 2016 18:32:16 GMT

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

Thejas M Nair commented on HIVE-14027:
--------------------------------------

This is happening only when UDF is in picture. If it is a select without UDF, the results
are correct.
Some more experiments I ran with hive 1.2.1 -

created a new table - tbl2
hive> select * from tbl2;
OK
2       two
ran  -
select a.n, a.t, concat(b.n, ""), concat(b.t, "") from (select * from tbl where n = 1) a  left
outer join  (select * from tbl2 where 1 = 2) b on a.n = b.n;
actually ran - select a.n, a.t, concat(b.n, ""), concat(b.t, "") from (select * from tbl where
n = 1) a  left outer join  (select * from tbl2 where 1 = 2) b on a.n = b.n;
ie replaced tbl with tbl2 for b in original query.
it gives -
1       one     1       NULL
looks like the join key is getting used as the input
ie . a.t is getting used as input
sorry, a.n is getting used as input to concat(b.n, "")
there might be an assumption somewhere that a.n and b.n are the same, since this is a join
but doesn't hold true for outer join

> NULL values produced by left outer join do not behave as NULL
> -------------------------------------------------------------
>
>                 Key: HIVE-14027
>                 URL: https://issues.apache.org/jira/browse/HIVE-14027
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Processor
>    Affects Versions: 1.2.1, 2.0.1
>            Reporter: Vaibhav Gumashta
>            Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string); 
> insert into tbl values (1, 'one'); 
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n = 1) a  left
outer join  (select * from tbl where 1 = 2) b on a.n = b.n;
> 1    one    false    true
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into tbl, and isnull
returns true in that case. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message