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

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
)

2. 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 :)

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! :)

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 )