[HIVE-13977] nvl funtion not working after left outer join

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.2.1
• Component/s: Hive
• Tags: hive
• Assignee: Unassigned
• Reporter: balaswamy vaddeman
• Created: 09/Jun/16 08:04
• Updated: 27/Feb/24 22:23
• Resolved: 11/Jul/16 17:20

Description

Recreating problem.

1).Create table with sample data.

create table tabletest (n bigint, t string);
insert into tabletest values (1, 'one');
insert into tabletest values(2, 'two');

2) Run leftouter join query on single table.

select a.n as leftHandN
, b.n as rightHandN
, b.t as rightHandT
, nvl(b.t,"empty") as rightHandTnvl – Expected empty --> received empty
, nvl(b.n,-1) as rightHandNnvl – Expected -1 --> received 1
from
(
select *
from tabletest
where n=1
) a
left outer join
(
select *
from tabletest
where 1=2
) b
on a.n = b.n;

nvl(b.n,-1) should return -1 but returns 1.

I have found b.n always returning a.n value.if a.n is 1 ,b.n is returning 1 and if it is 2,same 2 will be returned.

More information:

length(b.n) --gives-->1
cast(b.n as string) -gives-->1
ascii(b.n) -gives--->49 i.e 1

Issue Links

• duplicates: HIVE-14027 NULL values produced by left outer join do not behave as NULL