SQL Error 10249 Hive with multiple subqueries

user_10915410 0
Viewed 2575

I am trying to run multiple sub queries into a where clause, and I get the error below. Does mean that Hive doesn't support it? If not, is there a different way to write the query below?

If i wrote code like this:

SELECT * 
FROM ide_test.flights 
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights
WHERE year >1
);

I will get

[Error 10249]: Line 6:12 Unsupported SubQuery Expression 'carrier_code': SubQuery cannot use the table alias: flights; this is also an alias in the Outer Query and SubQuery contains a unqualified column reference

If i wrote like this,add an "s" besides the subquery or outer query table's name:

SELECT * 
FROM ide_test.flights 
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights s
WHERE year >1
);

or

SELECT * 
FROM ide_test.flights s
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights
WHERE year >1
);

Then it worked

hiveql hive sql
3 Answers

you can try by using corelated subquery 

  SELECT a.* 
  FROM ide_test.flights a
  where exists (SELECT 1
  FROM ide_test.flights a1
  WHERE a1.carrier_code=a.carrier_code
  and  carrier_code <>1
                )
user_68085830

You can do the same without IN subquery, using analytic function, this query will scan the table only once:

select s.* --list columns here
from
    (
     select f.*, 
            count(case when year >1 then 1 end) over(partition by carrier_code) cnt 
       from ide_test.flights f
    )s
where cnt>=1
;
user_27003440

It seems that hive sql parser can't recognize same table IN/NOT IN clause except use alias name for table name.

user_18352410
Related