0

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.

Improve this question
4
  • My guess would be that the two operands are different type (char vs signed bigint) so in order to compare them, they are both cast to float or double.
    – ypercubeᵀᴹ
    Commented Jun 28, 2022 at 23:37
  • The moral of this story: (1) don't store numbers in VARCHAR, (2) if you need numbers with lots of digit, use DECIMAL.
    – Rick James
    Commented Jun 29, 2022 at 2:03
  • Agreed. Obligatory "this database was here when I got here" ; )
    – Eric
    Commented Jun 29, 2022 at 2:19
  • What Rick said above, in different words: if you need long strings of digits as IDs, it's fine to store them as VARCHAR - as they are not really numbers (you would never add or multiply such IDs). But then let the applications treat them as strings as well.
    – ypercubeᵀᴹ
    Commented Jun 29, 2022 at 7:12

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Ah, the 11 exponent bits of FP64 explains the precision. I had done the napkin math to figure out 53 bits were needed for steps of 64 at this scale, but that only further confused me. +1 for clear and cheeky answer :)
    – Eric
    Commented Jun 29, 2022 at 0:03
  • @Eric - DOUBLE: 1 bit sign, 11 bits exponent (biased), 53 bits of precision, including a "hidden bit". Equivalent to about 16 decimal digits. FLOAT: 1 + 8 + 24; about 7 decimal digits. BIGINT UNSIGNED: 64 bits; BIGINT SIGNED: 63 bits. DECIMAL: up to 63 (or is it 64) decimal digits -- a lot bigger than DOUBLE and BIGINT.
    – Rick James
    Commented Jun 29, 2022 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.