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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.20
• OS: Windows (Windows XP professional)
• 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