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

0

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:

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?

  • Enter image description here
feita há 2 anos104 visualizações
1 Resposta
0

Correlated Subqueries don't support LIMIT

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

respondido há 2 meses

Você não está conectado. Fazer login para postar uma resposta.

Uma boa resposta responde claramente à pergunta, dá feedback construtivo e incentiva o crescimento profissional de quem perguntou.

Diretrizes para responder a perguntas