Sub-Queries in Hive – SemanticException [Error 10249]

Hive is supporting “Sub-Queries” functionality since version 0.13, as previously described in “Sub-Queries in Apache Hive.” We also discussed how the query syntax is similar to SQL. It’s no wonder, if you’ve encountered the following error message while working with sub-queries.

FAILED: SemanticException [Error 10249]: Line 1:24 Unsupported SubQuery Expression ‘ColName’: Correlating expression cannot contain unqualified column references.

Look at the below sub-query:

SELECT * FROM emp WHERE deptno IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);

The query above works OK in SQL, however it fails in Hive. This is because the column name for which the parent query is seeking for its value in the child query must have a table reference.

The query should be written as below.

SELECT * FROM emp WHERE emp.deptno IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);


Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s