ss - oscar

My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
SELECT DISTINCT returns wrong results with fixed width division on MySQL 5.1.30
ss - oscar
swanhart
Here the division between td1.c1 and td2.c2 is correct:
select td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |    -3300.0000 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)

Here DISTINCT is added to the query.  The result is incorrect:

select distinct td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |     -999.9999 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)


see MySQL bug#41814
http://bugs.mysql.com/bug.php?id=41814

The query returns correct (approximate) results on FLOAT columns:
select distinct td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03 
  from testdata2 td1, 
       testdata2 td2 
 where td1.c1 = -99 
   and td2.c2 between 0.02 and 0.04;
+------+------+-------------------+------------+
| c1   | c2   | td1.c1/td2.c2     | -99 / 0.03 |
+------+------+-------------------+------------+
|  -99 | 0.03 | -3300.00007376075 | -3300.0000 |
+------+------+-------------------+------------+
1 row in set (0.00 sec)
Tags: ,

  • 1
This kind of thing doesn't surprise me at all. I recently ran into a bug with NULL-safe checks against DATE/DATETIME fields but only on the right side of the operand. The workaround was to cast the date field to CHAR(10). Wheee....

IF (NOT (OLD.some_date <=> CAST(NEW.some_date AS CHAR(10)))) THEN

http://bugs.mysql.com/bug.php?id=36100
http://bugs.mysql.com/bug.php?id=40247

  • 1
?

Log in

No account? Create an account