Why does MySQL return multiple rows on a unique index (when casting)?

Question

Not sure if this is a bug, but when selecting from a table using a unique index (and implicit casting of type), multiple rows are returned, one of which is obviously wrong.

DROP TABLE IF EXISTS index_test;

CREATE TABLE index_test (
    string VARCHAR(40),
    number BIGINT,
    
    UNIQUE INDEX uq_string(string),
    UNIQUE INDEX uq_number(number)
) ENGINE = INNODB;

INSERT INTO index_test
(string, number)
VALUES
("42356411000001102", 42356411000001102),
("42356411000001104", 42356411000001104);

SELECT * FROM index_test WHERE number = 42356411000001102;      -- 1 Row        NATIVE BIGINT
SELECT * FROM index_test WHERE string = "42356411000001102";    -- 1 Row        NATIVE VARCHAR
SELECT * FROM index_test WHERE number = "42356411000001102";    -- 1 Row        CASTING
SELECT * FROM index_test WHERE string = 42356411000001102;      -- 2 ROWS!!!    CASTING

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html says:

The following rules describe how conversion occurs for comparison operations:

...

• 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.

Comparing a string to a numeric falls into the "all other cases" category. The numbers you show are cast to DOUBLE, but that doesn't have enough precision to represent such a large number without rounding off.

mysql> select cast('42356411000001102' as double) as `string`,
  cast(42356411000001104 as double) as `number`, 
  cast('42356411000001102' as double) = cast(42356411000001104 as double) as `matches`;

+---------------------+---------------------+---------+
| string              | number              | matches |
+---------------------+---------------------+---------+
| 4.23564110000011e16 | 4.23564110000011e16 |       1 |
+---------------------+---------------------+---------+

This behavior is independent of using an index, unique or not. I get the same result if I have no index defined in the test table.