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.

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.

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

Contenuto pertinente