CODEWITHSUNDEEP
hivehiveql
atomic_ice
atomic_ice

Reputation: 83

NOT IN subquery with hiveql retuning NullPointerException null

I'm trying to run a query in hive involving inlining 2 arrays in the same table and effectively taking the difference using NOT IN operator

select c1 from t1 
lateral view inline(m1) m1
where m1.key = 'x'
AND t1.c1 NOT IN
(
select c1 from t1
lateral view inline(m2) m2
where m2.key = 'y'
);

The above query returns

FAILED: NullPointerException null

Upvotes: 0

Views: 721

Answers (1)

sandeep rawat
sandeep rawat

Reputation: 4957

First filter out all c1 having value 'y'

With temp as
(select distinct c1 from t1
lateral view inline(m2) m2
where m2.key = 'y') 

select c1 from t1 
lateral view inline(m1) m1
where m1.key = 'x')
and c1 not in (select c1 from temp)

Upvotes: 1

Related Questions