MySQL incorrect equality casting bigint to char

All we need is an easy explanation of the problem, so here it is.

Why is the following statement true in MySQL?

CAST(544553000004545482 AS CHAR) = 544553000004545446

–Background–

I am querying a database with user IDs stored as VARCHAR(255), but my program represents these IDs as integers, so SQL is doing type coercion whenever I have a WHERE condition such as WHERE userId = %s. I noticed recently the above, incorrect result, because I got data for the user ending in 5482 when querying for the user ending in 5446.

I know the solution here is to either convert the database to a BIGINT column, or have my program cast to a string before querying, but I’d like to understand why the above is true? I’m guessing it has to do with a bit precision error. I noticed any value from 544553000004545441 – 544553000004545503 (inclusive, so a range of 63) registers as equal, so if SQL is converting somewhere to a lower bit int than the 2**64 of a BIGINT that could explain the truncation.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m shooting myself in the foot, why does it hurt?

This is by design:

In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

You are hitting the limit of representation of your values by double-precision floating point numbers, given the exponent value.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply