Query errors with Athena JDBC only

The issue you're experiencing with the Athena JDBC driver is unusual and seems to be related to how the driver is handling certain query structures, particularly the 'IN' clause. This problem appears to be specific to the JDBC driver implementation, as the same queries work fine through the Athena console and AWS SDK for Java.

The error message suggesting a mismatch at "line 28:42" regardless of the actual query length indicates that there might be some preprocessing or query transformation happening within the JDBC driver that's introducing this issue.

Here are some potential explanations and workarounds:

1. JDBC Driver Bug: This could be a bug in the JDBC driver version 3.5.0. It's possible that the driver is incorrectly parsing or transforming the query, especially when dealing with 'IN' clauses.

2. View Interaction: Since the table being queried is actually a view, there might be some unexpected interaction between the view definition and the JDBC driver's query handling.

3. Empty IN Clause: The fact that you can reproduce a similar error with an empty 'IN' clause suggests that the driver might be having trouble with the syntax or parameter binding for 'IN' statements.

Possible workarounds:

1. Try using an older version of the JDBC driver to see if the issue persists.

2. Instead of using 'IN', you could rewrite your queries using 'OR' conditions. For example: SELECT * FROM <tablename> WHERE id = '123' OR id = 'abc'

3. If possible, try materializing the view into a regular table to see if that resolves the issue.

4. For parameterized queries, ensure that you're properly binding parameters for the 'IN' clause.

5. If feasible, consider using the AWS SDK for Java for these problematic queries as a temporary workaround.

If these workarounds don't help, it would be advisable to contact AWS support with detailed information about your query, the JDBC driver version, and your Athena setup. They may be able to provide more specific guidance or confirm if this is a known issue with the driver.
Sources
Query fails (inconsistency) after last athena update | AWS re:Post
INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post

Responding to the AI answer:

Try using an older version of the JDBC driver to see if the issue persists.

We've tried multiple version of the JDBC driver all result in the same error

Instead of using 'IN', you could rewrite your queries using 'OR' conditions. For example: SELECT * FROM <tablename> WHERE id = '123' OR id = 'abc'

We also tried this and saw the same error.

If possible, try materializing the view into a regular table to see if that resolves the issue.

I considered this but didn't do it due to the complexity of that task and the unsuitability of this as a solution for us.

For parameterized queries, ensure that you're properly binding parameters for the 'IN' clause.

We tested this both with the 'IN' values parametrized and directly interpolated into the query string. Both result in the same error.

If feasible, consider using the AWS SDK for Java for these problematic queries as a temporary workaround.

We've done this and confirmed that it works (and this is the work-around solution we have in place right now), but there are big benefits to using JDBC that we lose with this approach.