MySQL Bugs: #19200: SELECT ... WHERE with decimal condition give wrong result

Bug #19200	SELECT ... WHERE with decimal condition give wrong result
Submitted:	19 Apr 2006 15:05	Modified:	5 May 2006 16:45
Reporter:	Marat Latypov	Email Updates:
Status:	Closed
Category:	Server: Docs	Severity:	S2 (Serious)
Version:	5.0.20, 4.1.19	OS:	Microsoft Windows (WinXP)
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

Description:
Select with decimal types in where clause sometimes give wrong result.

It is very hard to repeat bug. 
I made ready myisam table `mytable` specially for this. I'l try attach files later.

Tablу created with CREATE..SELECT instruction.
I haven't any reason why it does bug just in this case

May be it related with  http://bugs.mysql.com/bug.php?id=16272 ?

How to repeat:

mysql> select * from ;
+--------+
| price  |
+--------+
| 2.5400 |
| 2.8000 |
| 3.0900 |
| 3.4700 |
| 4.3200 |
+--------+
5 rows in set (0.00 sec)

mysql> select * from mytable where price=3.47;
Empty set (0.00 sec)

mysql> select * from mytable where price=3.4700;
Empty set (0.00 sec)

mytable.frm - for you can repeat bug

Attachment: mytable.frm (application/octet-stream, text), 8.36 KiB.

mytable.MYD - for you can repeat bug

Attachment: mytable.MYD (application/octet-stream, text), 65 bytes.

mytable.MYI and finally index file

Attachment: mytable.MYI (application/octet-stream, text), 1024 bytes.

Thank you for a bug report. Verified with your table files attached (and with a copy of
that table, created the same CREATE ... SELECT way). It is a bug.

As a workaround, use " ...= '3.4700'; ". Works for me.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5684

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

The table whose data files are attached to this bug report appear to have been created
with a server binary older than 5.0.3.

As described in section A.5.8 of MySQL's Reference Manual
(http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html) before version 5.0.3
the DECIMAL data were treated as non-exact floating point data and hence the direct
comparison may not hold.
The reason this behaves the same way even with newer servers is that MySQL preserves the
old behavior for stored data. As a result every value coming from a table created using a
pre-5.0.3 binary will be treated as non-exact value. 
If the table is dumped and recreated using a current binary, it will be created using the
post-5.0.3 semantics and the DECIMAL columns will be treated as an exact type.

Resetting to documention issue.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated these sections to indicate that older tables
with DECIMAL will get the old behavior until the tables are
updated:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html