Athena: Reference left-hand row in right-hand subquery? (correlated subquery, CROSS JOIN LATERAL)

I have a usecase where I need to support recursive relationship fetching, with a possible LIMIT clause on each depth of relationship.

Something like:

SELECT ... FROM artists LIMIT 5
for artist in artists:

    SELECT ... FROM albums WHERE artist_id = artist.id LIMIT 3
    for album in albums:

        SELECT ... FROM tracks WHERE album_id = albums.id LIMIT 2

In Postgres/MySQL etc, you can do this with LATERAL/CROSS APPLY.

select *
from
  lateral (
    select ...
    from `Artist`
    limit 5
  ) as `Artist`,

  lateral (
    select ...
    from `Album`
    where `Artist`.`ArtistId` = `Album`.`ArtistId`
    limit 3
  ) as `Album`

Athena says that it supports CROSS JOIN LATERAL:

• https://docs.aws.amazon.com/athena/latest/ug/engine-versions-reference.html

However if you try to use this with a correlated subquery, it wigs out Is there any way to emulate the behavior of LATERAL/CROSS APPLY with the variant of SQL that Athena V2 supports?

•