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
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’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 🙂