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?

已提问 3 个月前350 查看次数
3 回答
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
已回答 3 个月前
  • 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
已回答 3 个月前
专家
已审核 3 个月前
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 )
已回答 3 个月前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则

相关内容