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