Nested subquery not supported in hive

Question

We have tried the below query in hive. but getting the error. please help me to resolve this in any other way.

select count(1) as OpenItems from issues i , issue_statuses s  

where s.id = i.status_id 

and  s.name NOT IN ('Closed','Passed','Rejected','On 

Hold','Baselined','Completed')

and i.project_id IN 

(select id from projects3 from

CASE WHEN ${projectname} = 'All' then id in

(select p.id from members m, projects3 p ,users_1 u

where m.project_id = p.id and u.id = m.user_id and u.status = '1'

and u.id IN 
(select u1.id from users_1 u1, Supervisor_hierarchy s1 where u1.mail = s1.email and s1.name = ${Superisorname})
group by p.id)
WHEN (${projectname} <>'All' and ${SubProject projectname} ='All') then id 
IN (select id from (select id from project_closure where parent_id in (select id from projects where name = ${projectname}) group by id)a)
WHEN (${SubProject projectname}<>'All' and ${projectname}<> 'All') then id 
IN (select id from(select id from project_closure where id in (select id from projects  where name = ${SubProject projectname}) group by id)a)
END
order by id)

error: 6:5 Unsupported SubQuery Expression 'id': SubQuery expression refers to both Parent and SubQuery expressions and is not a valid join condition.

Hi manjula .. is this issue resolved? I am facing with the same issue .. — MysticForce

Munesh Munesh · Accepted Answer · 2018-03-14T20:16:13

I know it is late but posting for anyone who face this issue.

This issue occurs when we encounter one or more of the below limitations of Hive Subqueries.

In this scenario, the reference to the parent query is used in Group By clause which comes under the 4th limitation.

Hive Subquery Limitations

These subqueries are only supported on the right-hand side of an expression.
IN/NOT IN subqueries may only select a single column.
EXISTS/NOT EXISTS must have one or more correlated predicates.
References to the parent query are only supported in the WHERE clause of the subquery.

Source: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

Nested subquery not supported in hive

1 Answers