Athena Prepared Statement Parameter Order-CodePudding

I'm attempting to use the Athena parameterized queries feature in conjunctions with CTEs.

If I run a simple Athena CTE select statement like this, I get back no results even though I should get a single row back with a single value of 1:

aws athena start-query-execution  \
  --query-string "WITH cte as (select 1 where 1 = ?) select * from cte where 2 = ?" \
  --query-execution-context "Database"="default"  \
  --result-configuration "OutputLocation"="s3://athena-test-bucket/" \
  --execution-parameters "1" "2"

# Output CSV:
# "_col0"

Strangely, if I reverse the parameters, the query works correctly:

aws athena start-query-execution  \
  --query-string "WITH cte as (select 1 where 1 = ?) select * from cte where 2 = ?" \
  --query-execution-context "Database"="default"  \
  --result-configuration "OutputLocation"="s3://athena-test-bucket/" \
  --execution-parameters "2" "1"

# Output CSV:
# "_col0"
# "1"

The AWS docs state that:

In parameterized queries, parameters are positional and are denoted by ?. Parameters are assigned values by their order in the query. Named parameters are not supported.

But this clearly doesn't seem to be the case, as the query only acts as expected when the parameters are in reversed order.

How can I get Athena to correctly insert parameters in the standard, first-to-last, positional way that's typical of other DBs? While the above example is simple, in reality, I have queries with arbitrarily nested CTEs with arbitrary amounts of parameters.

CodePudding user response：

This is a know issue (incorrect parameters order for prepared statements using WITH clause) in old versions of Presto (link 1, link 2) and Trino (link) which was fixed. For Presto in version 0.272 (fix PR):

Fix parameter ordering for prepared statement queries using a WITH clause.

Sadly at the moment of writing Athena engine version 2 is based on Presto 0.217 so you need to workaround by changing the parameters order.