Boto3 Is Gaslighting Me About My Athena Query Parameters

Okay, so I have to write this down before I forget and lose my mind all over again.

For the last couple of hours, I’ve been in a bare-knuckle brawl with boto3 and AWS Athena. It’s one of those bugs that makes you question everything you thought you knew. It’s the kind of bug that makes you want to close your laptop and take up farming.

Look, the setup was simple. I have a products table in Athena, created from some Parquet files. In that table, there’s a product_id column. It’s a VARCHAR (or string, same thing). It has to be a string because some of our product IDs have letters in them, but a lot of them are just numbers, like old UPC codes. For example, a valid ID is '9780143126560'. It looks like a number, it quacks like a number, but its a string. The schema says so. I say so.

So I write my little Python function to fetch a product.

# This is my simple, elegant, and VERY WRONG code
product_id = '9780143126560'
response = athena_client.start_query_execution(
    QueryString="SELECT * FROM products WHERE product_id = ?",
    ExecutionParameters=[product_id],
    # ... the rest of the config
)

I run it. And BAM.

Athena query failed: TYPE_MISMATCH: Cannot apply operator: varchar = integer

I stare at the screen. “Integer? What integer? I gave you a Python string. The column is a VARCHAR. Where are you getting integer from?”

At this point, I’m just annoyed. I think, “Fine, whatever, boto3 is being weird. I’ll be super explicit.” You know, just spell it out for the machine.

# Attempt #2 - Let's be explicit! (Still wrong)
response = athena_client.start_query_execution(
    QueryString="SELECT * FROM products WHERE CAST(product_id AS VARCHAR) = ?",
    ExecutionParameters=[product_id],
    ...
)

I run it. Same. Damn. Error. varchar = integer.

This is where things start to get weird. My grip on reality is slipping. I’m thinking, “Did the crawler mess up teh schema? Am I querying the wrong table?” I go check the schema in the Glue Data Catalog for the fifth time. Nope, clear as day: product_id: string.

I even remember this one time in a previous job where a similar issue with a database driver drove a senior engineer to write a 10-page document detailing the bug. We all thought he was crazy- until he was proven right. I feel that engineer’s spirit in me now.

So I try all the other tricks. Maybe I can force the parameter to be a string with concatenation?

# Attempt #3 - The "I'm getting desperate" query (Also wrong)
response = athena_client.start_query_execution(
    QueryString="SELECT * FROM products WHERE product_id = ? || ''",
    ExecutionParameters=[product_id],
    ...
)

Now I get a new error! Progress! Except the error is even more insane: Unexpected parameters (integer, varchar(0)) for function concat.

And that’s when it finally clicked. The error message was telling the truth all along. Boto3 was sending an INTEGER. The problem wasn’t in my SQL. The problem was that the boto3 driver is trying to be “smart”. It looks at my parameter value - '9780143126560' - sees it’s all digits, and helpfully decides, “Oh, that’s an integer!” before it even sends it to Athena. So all my SQL tricks were useless because the parameter was already typed incorrectly before it even hit the query engine.

So, how do you fix it? How do you outsmart the “smart” driver?

You have to make the parameter value itself so obviously a string that boto3 doesn’t dare touch it. And you do it like this- and I know, this looks insane.

The Solution:

# The actual, bonkers, but working solution
product_id = '9780143126560'
response = athena_client.start_query_execution(
    QueryString="SELECT * FROM products WHERE product_id = ?",
    # This is the magic. Put the CAST in the parameter itself.
    ExecutionParameters=[f"CAST('{product_id}' AS VARCHAR)"],
    ...
)

You have to put the CAST operation inside the parameter string.

Boto3 looks at the value CAST('9780143126560' AS VARCHAR), sees a bunch of letters and quotes, and says, “Okay, I give up, that’s definitely not an integer.” It sends the whole expression as a string literal to Athena. Athena then plugs that expression into the ?, and the database itself runs the cast, resulting in a proper VARCHAR to VARCHAR comparison.

It’s ridiculous. It’s counter-intuitive. And it works.

Anyway, hope this saves someone from a few hours of banging their head against a wall. My sanity is mostly intact. Mostly.