Bug #4620 != or <> returns rows that DO match when I add fields together
Submitted: 19 Jul 2004 6:32 Modified: 22 Jul 2004 15:38
Reporter: Steffan Packer Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.20 OS:Microsoft Windows (Windows XP professional)
Assigned to: CPU Architecture:Any

[19 Jul 2004 6:32] Steffan Packer
Description:
When I run this query:
SELECT DealerNbr, val1, (HPCurrExpo+CSCurrExpo+OLCurrExpo+FLCurrExpo) AS Calc
FROM `s_monthly_trends`
WHERE (HPCurrExpo+CSCurrExpo+OLCurrExpo+FLCurrExpo) != CurrentExposure
AND MonthTo='2004-06-30'

I get 98 out of a possible 1313 records returned as not matching:

+-----------+-----------------+-------------+
| DealerNbr |          val1          | Calc        |
+-----------+-----------------+-------------+
|        51 |       149275.73 |   149275.73 |
|       224 |      3808683.51 |  3808683.51 |
|       239 |      4085458.03 |  4085458.03 |
|       240 |      1457728.67 |  1457728.67 |
|       241 |      4953430.40 |  4953430.40 |
...
...
...

all 98 records appear to match and the field types of currentExposure and the fileds being added are identical! (decimal(13,2) NOT NULL)

I get the same result if I use <. instead of !=, if I use = I get the remaining 1215 records returned....

How to repeat:
I get the same result everytime I run the query
[22 Jul 2004 15:38] Alexander Keremidarski
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:

Calculations with DECIMAL columns are done with floating point aritmetic so all problems with floating point accuracy apply in your case.

WHERE (HPCurrExpo+CSCurrExpo+OLCurrExpo+FLCurrExpo) != CurrentExposure

The result of
(HPCurrExpo+CSCurrExpo+OLCurrExpo+FLCurrExpo)  is of type DOUBLE

http://dev.mysql.com/doc/mysql/en/Problems_with_float.html