MySQL returning multiple rows that does not match search criteria

Question

I have MySQL installed locally, running SELECT VERSION() returns this value: 5.6.43-84.3

When I run a query it is returning multiple rows when it should only return 1 row. Let me set it up, it's easier to explain that way.

1. Create a test table:

    CREATE TABLE test_table 
    (
        test_val VARCHAR(255)
    )
    ;

2. Load 3 values into the table:

    INSERT INTO test_table (test_val)
    VALUES 
    ('9671986020630615'),
    ('9671986020630616'),
    ('9671986020630617')
    ;

3. Run this query (This query returns 1 row which is expected):

    SELECT * 
    FROM test_table
    WHERE test_val = '9671986020630615'
    ;

4. Run this query (This query returns 3 rows, which it shouldn't):

    SELECT * 
    FROM test_table
    WHERE test_val = 9671986020630615
    ;

Here's what I have observed about this situation:

1. The first query surrounds the value in the WHERE clause with single quotes.
2. The second query does not surround the value in the WHERE clause with single tics.
3. The column in the test table is defined as VARCHAR(255)
4. It makes sense that the first query returns just one row because it's comparing a string from the WHERE clause to a string value in the test table (VARCHAR(255))
5. Something is happening when MySQL compares the numerical value in the WHERE clause of the second query to the string value in the test table (VARCHAR(255)) which is causing MySQL to return 3 rows instead of just 1.

It makes sense that the first query returns the correct result because it is comparing a string to a string.

It also makes a degree of sense that the second query is returning a bad dataset (3 rows as opposed to the 1 row it should return).

But my question is why is MySQL doing this? Why when it compares a number to 3 different VARCHAR(255) values does it return all 3 rows when the true value of the numerical value in the WHERE clause only matches 1 row?

So, in essence for the first query MySQL is saying:

'9671986020630615' = '9671986020630615',

'9671986020630615' <> '9671986020630616',

'9671986020630615' <> '9671986020630617'

but for the second query it is saying:

9671986020630615 = '9671986020630615',

9671986020630615 = '9671986020630616',

9671986020630615 = '9671986020630617'

Any help will be much appreciated.

Answer 1

MySQL handles all numbers internally the same way Javascript does, with IEEE double-precision floating point representation.

When you omit the quotation marks from your long numeric strings, that is you write 9671986020630615 in place of '9671986020630615 ', MySQL uses the number. Then, when it runs the WHERE part of your query, it silently coerces each column value to a double precision number.

But due to the machine epsilon -- the limit of precision -- of double precision, 9671986020630615, 9671986020630616, and 9671986020630617 all have the same value. So the WHERE finds all three.

CAST(9671986020630615 AS DOUBLE) CAST(9671986020630616 AS DOUBLE) CAST(9671986020630617 AS DOUBLE) 
     9.671986020630616e15             9.671986020630616e15             9.671986020630616e15 | 

See how all three integers have the same representation as DOUBLE?