| Bug #23208 | Comparison of values fails | ||
|---|---|---|---|
| Submitted: | 12 Oct 2006 11:17 | Modified: | 12 Oct 2006 12:29 |
| Reporter: | klo klo | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1 and 5.0.22 | OS: | Linuz |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | comparison, double, like | ||
[12 Oct 2006 11:26]
Andrey Hristov
Floats are differently represented and are not as precise as ints. Usually floats are never directly compared like f1 = f2, but subtracted like f1 - f1 < e , where e is a very small number (epsilon). Here is the output on my machine to see that 70.68 in memory is not 70.68 but a bit different number: mysql> create table abc (a float); Query OK, 0 rows affected (3.62 sec) mysql> insert into abc values (70.68); Query OK, 1 row affected (0.07 sec) mysql> select * from abc; +-------+ | a | +-------+ | 70.68 | +-------+ 1 row in set (0.02 sec) mysql> select a*1000000 from abc; +-----------------+ | a*1000000 | +-----------------+ | 70680000.305176 | +-----------------+ 1 row in set (0.09 sec) mysql> select a*100 = 7068 from abc; +--------------+ | a*100 = 7068 | +--------------+ | 0 | +--------------+ 1 row in set (0.08 sec)
[12 Oct 2006 11:27]
Andrey Hristov
mysql> select a*100 - 7068 from abc; +------------------+ | a*100 - 7068 | +------------------+ | 3.0517578125e-05 | +------------------+ 1 row in set (0.01 sec)
[12 Oct 2006 12:29]
klo klo
So if the comparison fails with 70.68 why not with 70.67 or 70.69 ?
Description: There are 3 different double values in my database. mysql> select * from klo; +-------+ | id | +-------+ | 70.67 | | 70.68 | | 70.69 | +-------+ 3 rows in set (0.00 sec) Selecting as follows gives the following results: mysql> select id*100 from klo where id*100='7067'; +--------+ | id*100 | +--------+ | 7067 | +--------+ 1 row in set (0.00 sec) mysql> select id*100 from klo where id*100='7068'; Empty set (0.00 sec) mysql> select id*100 from klo where id*100='7069'; +--------+ | id*100 | +--------+ | 7069 | +--------+ 1 row in set (0.00 sec) So for '7068' there is no result even though it should. We used the same queries for all three questions and got two different resultsets. But if we use a 'like' than a '=' we get the correct resultset: mysql> select id*100 from klo where id*100 like '7068'; +--------+ | id*100 | +--------+ | 7068 | +--------+ How to repeat: mysql> create table klo(id double); mysql> insert into klo(id) values('70.67'); mysql> insert into klo(id) values('70.68'); mysql> insert into klo(id) values('70.69'); mysql> select * from klo; +-------+ | id | +-------+ | 70.67 | | 70.68 | | 70.69 | +-------+ 3 rows in set (0.00 sec) mysql> select id*100 from klo where id*100='7067'; +--------+ | id*100 | +--------+ | 7067 | +--------+ 1 row in set (0.00 sec) mysql> select id*100 from klo where id*100='7068'; Empty set (0.00 sec) ^^^^^^^^^^^^^^^^^^^^ mysql> select id*100 from klo where id*100='7069'; +--------+ | id*100 | +--------+ | 7069 | +--------+ 1 row in set (0.00 sec) mysql> select id*100 from klo where id*100 like '7068'; +--------+ | id*100 | +--------+ | 7068 | +--------+ Suggested fix: Sorry but there is no suggestion.