How to go around the following Athena limitation with subqueries in unnest

0

The following query returns an error

SELECT * from UNNEST(SEQUENCE(
        (SELECT min(col1) from table1),
        current_date,
        INTERVAL '1' DAY
    ))

but this query works just fine

SELECT SEQUENCE(
        (SELECT min(col1) from table1),
        current_date,
        INTERVAL '1' DAY
    )

What am i missing? Any way around this?

posta 3 mesi fa353 visualizzazioni
3 Risposte
1

Thanks for trying and providing more context. In that case, the only other option I can think of is to break down the query into different ones:

  1. Retrieve the minimum value from the table:
WITH min_value AS (
    SELECT min(t_alert_created) AS min_date
    FROM testyoni_alerts
)
  1. Use the min_value derived table:
SELECT *
FROM UNNEST(
    SEQUENCE(
        (SELECT min_date FROM min_value),
        current_date,
        INTERVAL '1' DAY
    )
)

Let me know if that works :)

AWS
con risposta 3 mesi fa
  • that too doesn't work. same error. it can't resolve "min_value" from that context

1

Hello Yoni,

Thank you very much for your question. The error you are obtaining happens because in Athena, the UNNEST function expects an array as input, but the SEQUENCE function returns a row type. To work around this, you can use the STACK function in Athena, which is designed to convert row types into arrays.

You can try the following query to check if it works in Athena:

SELECT *
FROM UNNEST(STACK(
    SEQUENCE(
        (SELECT min(col1) from table1),
        current_date,
        INTERVAL '1' DAY
    )
))

The STACK function takes the row type returned by the SEQUENCE function and converts it into an array, which can then be properly handled by the UNNEST function. I hope it helps and let me know if that works! :)

AWS
con risposta 3 mesi fa
ESPERTO
verificato 3 mesi fa
0

This is not the case.

  1. "STACK" doesn't seem to be a function in Presto/Athena
  2. more to the point you can in general unnest a sequence. for instance, this query works
SELECT *
    FROM UNNEST(
        SEQUENCE(
            current_date - interval '1' year,
            current_date,
            INTERVAL '1' DAY
        )
    )

The issue is when you need to subquery for a parameter of the sequence, as in the example i provided before. Specifically this query

SELECT *
    FROM UNNEST(
        SEQUENCE(
            (SELECT min(t_alert_created) from testyoni_alerts),
            current_date,
            INTERVAL '1' DAY
        )
    )

results in

GENERIC_INTERNAL_ERROR: Unexpected subquery expression in logical plan: (SELECT min(t_alert_created) FROM testyoni_alerts )
con risposta 3 mesi fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande