MySQL Bugs: #28979: Floats/Doubles in scientific notation don't work in WHERE clauses

Bug #28979	Floats/Doubles in scientific notation don't work in WHERE clauses
Submitted:	8 Jun 2007 15:13	Modified:	20 Jun 2007 8:57
Reporter:	Craig Holmquist	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.44-BK, 5.0.41	OS:	Windows
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
Execute the following SQL code:

CREATE DATABASE testfl;
USE testfl;
CREATE TABLE testfl (f DOUBLE NOT NULL);
INSERT INTO testfl VALUES (42.0164375305175780);
SELECT * FROM testfl WHERE f = 42.0164375305175780;
SELECT * FROM testfl WHERE f = 4.20164375305175780e+001;

In MySql 4.0.16, both SELECTs work.  In MySQL 5.0.41, only the first one works.  If this was an intentional change in going from MySQL 4 to 5, I can't find it documented anywhere.

How to repeat:
See above.

Suggested fix:
Handle Floats/Doubles in scientific notation the same way as they were handled in MySQL 4.0.

Thank you for a bug report. Verified just as described with latest 5.0.44-BK on Linux. The problem is that 2 values in scientific notation behaves differently(!) in WHERE, but are represented as same in SELECT list. Comapre:

mysql> SELECT *,  4.2016437530517578e+001 FROM testfl;
+------------------+-------------------------+
| f                | 4.2016437530517578e+001 |
+------------------+-------------------------+
| 42.0164375305176 |         42.016437530518 |
+------------------+-------------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM testfl WHERE f = 4.2016437530517578e+001;
+------------------+
| f                |
+------------------+
| 42.0164375305176 |
+------------------+
1 row in set (0.01 sec)

Here we have rounding, but row is selected. Here:

mysql> SELECT * FROM testfl WHERE f = 4.20164375305175780e+001;
Empty set (0.00 sec)

mysql> SELECT *,  4.20164375305175780e+001 FROM testfl;
+------------------+--------------------------+
| f                | 4.20164375305175780e+001 |
+------------------+--------------------------+
| 42.0164375305176 |          42.016437530518 |
+------------------+--------------------------+
1 row in set (0.00 sec)

in SELECT list value is represednted as the same as before after rounding, but it is considered differently by WHERE. Thus, this is a bug.

This is not a bug. See
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html