pyathena.error.DatabaseError: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:3242: mismatched input 'OFFSET'. Expecting: <EOF>

[berendovychRB]

The documentation says OFFSET is supported https://docs.aws.amazon.com/athena/latest/ug/select.html

but I have such an error, sending the query with LIMIT and OFFSET

pyathena.error.DatabaseError: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:3242: mismatched input 'OFFSET'. Expecting: <EOF>

Query example: SELECT "users"."id", "users""created_at" FROM "users" ORDER BY "users"."id" DESC LIMIT 30 OFFSET 60

[laughingman7743]

Are you using SQLAlchemy? If so, please check the OFFSET and LIMIT tests. https://github.com/laughingman7743/PyAthena/blob/cb901047861cda020d481430d08699300e61831d/tests/pyathena/sqlalchemy/test_base.py#L286-L290

If you're not using SQLAlchemy, then I think there's a problem with the syntax of your query. The LIMIT clause should probably be the last.

SELECT "users"."id", "users""created_at" FROM "users" ORDER BY "users"."id" DESC OFFSET 60 LIMIT 30

https://docs.aws.amazon.com/athena/latest/ug/select.html

Synopsis

[ WITH with_query [, ...] ] SELECT [ ALL | DISTINCT ] select_expression [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ] [ HAVING condition ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ] [ OFFSET count [ ROW | ROWS ] ] [ LIMIT [ count | ALL ] ]

[berendovychRB]

The correct sequence of parameters helped me, thanks!