MariaDB inconsistent comparison results between DOUBLE(10,2) and string

Question

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)

Answer 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

Answer 2

Consider also

ROUND('4.94', 2)

Or

FORMAT('4.94', 2)

Answer 3

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.