Athena Query - Correlated subquery not supported

Question

This query works outside of Athena (in DB2). For the most part it's working in Athena when I comment out the "having max(column1) > 3" line. But, the error that Athena returns when I RUN, is saying SYNTAX_ERROR: line 23:9: Given correlated subquery is not supported. The line corresponds with "and not exists":

```
select
*
from t1
where
product = 'SHIRT'
and t1.year = '2022' and t1.month = '07' and t1.day ='31'
and not exists
(
select max(column1) from a2
        where trim(a2.id) = trim(t1.id) --these are strings with trailing spaces, hence the trim here
        having max(column1) > 3
        )
limit 10
```

It's not like Athena hates the having clause because I wrote a statement as such and it worked:
select max(column1) from a2
        having max(column1) > 3

It's not the "and not exists" statement because I have used that previously and it worked.

I cannot figure out what Athena is fussing about.

Shubh · Answer

NOT EXISTS  is indeed supported in presto and thus in Athena too. The error is also not very descriptive to conclude the reason. I found one issue with your query that you are using HAVING clause without GROUP BY. Try to make amendment's in your query based on your requirements.

jschwar313 · Answer

I've run into the same thing in Hive, but I'm not familiar with Athena.  Try coding the A2 table as a join statement, not in the where clause and then in the where clause do the filtering.  Let me know if that works.  Here's an example of how that's done.  
https://stackoverflow.com/questions/37215092/working-around-unsupported-correlated-where-subqueries-in-hive

Athena Query - Correlated subquery not supported

Relevant content