Athena Query: No viable alternative at input 'array(select'

user_15168738 0
Viewed 195

I am running a simple aws athena query as follows

SELECT elements,
    ARRAY(SELECT v * 2
          FROM UNNEST(elements) AS v) AS multiplied_elements
FROM (
    VALUES
        (ARRAY[1, 2]),
        (ARRAY[1, 3, 9]),
        (ARRAY[1, 4, 16, 64])
) AS t(elements)

The query is directly taken from a blog in the official prestodb website: https://prestodb.io/blog/2020/03/02/presto-lambda

But on running the query I am running into

line 2:11: no viable alternative at input 'array(select' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 3fec5bf2-571a-45b0-8771-a860b6e840ec; proxy: null)

What is the reason for this error?

trino presto amazon-athena
1 Answers

Presto and Athena (which is based on a very old version of Presto) don't actually support that syntax. In recent versions of Trino (formerly known as Presto SQL), you can almost do that, except for the lack of "array by table subquery constructor" syntax:

SELECT 
    elements,
    ( -- this is a correlated scalar subquery 
        SELECT array_agg(v * 2)
        FROM UNNEST(elements) AS u(v)
    ) AS multiplied_elements
FROM (
    VALUES
        (ARRAY[1, 2]),
        (ARRAY[1, 3, 9]),
        (ARRAY[1, 4, 16, 64])
) AS t(elements);

It outputs:

    elements    | multiplied_elements
----------------+---------------------
 [1, 2]         | [2, 4]
 [1, 3, 9]      | [2, 6, 18]
 [1, 4, 16, 64] | [2, 8, 32, 128]
(3 rows)
user_29587520
Related