Athena engine version 3 Responding with Given correlated subquery is not supported for cross join and subquery

Question

Since upgrade to Athena engine version 3 a query with a subquery in the where clause is no longer supported.  Athena responds with, "NOT_SUPPORTED: line 24:6: Given correlated subquery is not supported".

Example:

```
select
  sup1.dynamodb.keys.something.s something1,
  site.m.something.s something2,
  count(1) count
from
  "prefs" as sup1
  cross join unnest(sup1.dynamodb.newimage.somethings.l) as t(something)
where
  1 = 1
  and sup1.partition_0 = '2023'
  and sup1.partition_1 = '06'
  and sup1.partition_2 = '27'
  and sup1.partition_3 =
    (select
      max(sup2.partition_3)
    from
      "prefs" as sup2
    where
      sup2.partition_0 = sup1.partition_0
      and sup2.partition_1 = sup1.partition_1
      and sup2.partition_2 = sup1.partition_2
      and sup2.dynamodb.keys.something.s = sup1.dynamodb.keys.something.s)
group by
  sup1.dynamodb.keys.something.s,
  site.m.something.s
having
  count(1) > 1
order by
  1

```

Anish P · Answer

Thank you Jason for reporting it. The team is aware of the issue and are working on the fix.

jeremy simmons · Answer

Correlated Subqueries don't support LIMIT

```sql
SELECT *
FROM (VALUES 1, 2, 3) table1(a)
LEFT JOIN LATERAL
(SELECT b FROM (VALUES 2,4) table2(b) WHERE b > a limit 1) ON TRUE
```
https://github.com/trinodb/trino/issues/4673

Athena engine version 3 Responding with Given correlated subquery is not supported for cross join and subquery

Relevant content