0

I'm searching a table for an exact match on an amount column DOUBLE(10,2). Due to the database driver I'm using, I have to bind my amount as a string parameter. In general this works well, hoever this leads to certain values not being found.

For example, when comparing 3.94 to '3.94':

SELECT CAST('3.94' AS DOUBLE(10,2)) = '3.94' `comp`, CAST('3.94' AS DOUBLE(10,2)) `cast`, '3.94' `string`;

The database considers them to be the same:

comp  cast  string
   1  3.94    3.94

However, when comparing 7.94 to '7.94':

SELECT CAST('4.94' AS DOUBLE(10,2)) = '4.94' `comp`, CAST('4.94' AS DOUBLE(10,2)) `cast`, '4.94' `string`;

The database considers them to be different:

comp  cast  string
   0  4.94    4.94

In both cases, what I am being shown looks like the same value, even when they don't match. My presumption is that there is a rounding error going on somewhere but I don't fully understand the reason for the failure.

I would also like to know if there are any better/alternative solutions to resolve this other than:

SELECT *
FROM `my_amounts_table`
WHERE
`amount` = CAST(:amount AS DECIMAL(10,2));

EDIT

Apparently you should never do equality checks on floating point numbers.

In my above cases would I need to do something more like this to find values match 7.94:

SELECT *
FROM `my_amounts_table`
WHERE
(`amount` > 7.93 AND `amount` < 7.95)
Improve this question
9
  • 4
    You should never search for floating point values by an exact value, as they often don't have an exact decimal representation. Always use a range or a rounding difference. And never search it using a string. See also dev.mysql.com/doc/refman/8.3/en/type-conversion.html If you need to cast, then cast it to the same type as the column. Commented Mar 22, 2024 at 11:32
  • 1
    Take a look at float.exposed Commented Mar 22, 2024 at 11:47
  • 1
    That is an excellent resource! Thank you. Commented Mar 22, 2024 at 11:51
  • 1
    the mysql documentation is not a good resource for a mariadb question. Commented Mar 22, 2024 at 13:39
  • 2
    @ysth Problem is that the MariaDB is an even worse resource, this is not documented there at all. At least MariaDB is a fork of MySQL, so data types mostly behave the same. Commented Mar 22, 2024 at 16:57

3 Answers 3

Reset to default
1

DOUBLE (and FLOAT) cannot represent most values exactly. Use DECIMAL, which is designed to do so, instead:

SELECT
    CAST('4.94' AS DECIMAL(10,2)) = '4.94' `comp`,
    CAST('4.94' AS DECIMAL(10,2)) `cast`, '4.94' `string`;

Your column type should very likely be DECIMAL in the first place.

fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is not a good answer. The comparison should use a number range, not equality.
@TheImpaler what case do you think that will improve?
@TheImpaler a number range is exactly what casting to a decimal type does. dbfiddle.uk/NjQndPES
1

When storing 'money', declare the column DECIMAL(11,2) (or some suitable size). Then all these questions and discussions and CASTs and ROUNDs go away. '3.94' with or without quotes stores the same and compares as expected.

Improve this answer

1 Comment

almost all currencies worldwide use 2 decimals, but there are a few exceptions. make sure to evaluate which currencies you need to support when deciding on a suitable size.
-1

Consider also

ROUND('4.94', 2)

Or

FORMAT('4.94', 2)
Improve this answer

1 Comment

This is not a good answer. The comparison should use a number range, not equality.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.