MySQL Bugs: #4077: mysql: deletion of records buggy?

Bug #4077	mysql: deletion of records buggy?
Submitted:	9 Jun 2004 13:37	Modified:	29 Jul 2004 0:42
Reporter:	Bauke Jan Douma	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQLCC	Severity:	S2 (Serious)
Version:	mysql-4.0.20 custom compile	OS:	Linux (Linux-2.6)
Assigned to:	Jorge del Conde	CPU Architecture:	Any

Description:
Why does the following data and the following script piped
through mysql leave us with four records still present??

The same happens when I select all records from within mysqlcc,
then delete records.

When, on the other hand I execute:
DELETE * from `xxx`
all records are deleted.

[note: database data comes from a SQL tutor on the net] 

  
>$ cat checks
1,Ma Bell,150,Have sons next time
2,Reading R.R.,245.34,Train to Chicago
3,Ma Bell,200.33,Cellular Phone
4,Local Utilities,98,Gas
5,Joes Stale X Dent,150,Groceries
16,Cash,25,Wild Night Out
17,Joans Gas,25.1,Gas
9,Abes Cleaners,24.35,X-Tra Starch
20,Abes Cleaners,10.5,All Dry Clean
8,Cash,60,Trip to Boston
21,Cash,34,Trip to Dayton
30,Local Utilities,87.5,Water
31,Local Utilities,34,Sewer
25,Joans Gas,15.75,Gas

$> cat script
CREATE DATABASE IF NOT EXISTS checks;
USE checks;

drop table if exists xxx;

CREATE TABLE xxx
(
        checknum INT NOT NULL,
        payee VARCHAR(64),
        amount FLOAT DEFAULT 0,
        remarks VARCHAR(64)
) TYPE = MyISAM;

LOAD DATA
INFILE '/root/.mysql/checks'
INTO TABLE `xxx`
fields terminated by ',';
select * from `xxx`;

# this works flawlessly:
# delete * from `xxx`   

# this leaves four records, whether it be run from the
# command-line in this script, or from within mysqlcc 
# after selecting all 14 records, then excuting 'delete'
# (in fact, the following lines are a saved history from
# mysqlcc on exactly that command.
# 
DELETE FROM `xxx`
WHERE `checknum`=1 AND `payee`='Ma Bell' AND ABS(`amount` - 150) < 1e-31 AND `remarks`='Have sons next time'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=2 AND `payee`='Reading R.R.' AND ABS(`amount` - 245.34) < 1e-31 AND `remarks`='Train to Chicago'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=3 AND `payee`='Ma Bell' AND ABS(`amount` - 200.33) < 1e-31 AND `remarks`='Cellular Phone'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=4 AND `payee`='Local Utilities' AND ABS(`amount` - 98) < 1e-31 AND `remarks`='Gas'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=5 AND `payee`='Joes Stale X Dent' AND ABS(`amount` - 150) < 1e-31 AND `remarks`='Groceries'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=16 AND `payee`='Cash' AND ABS(`amount` - 25) < 1e-31 AND `remarks`='Wild Night Out'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=17 AND `payee`='Joans Gas' AND ABS(`amount` - 25.1) < 1e-31 AND `remarks`='Gas'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=9 AND `payee`='Abes Cleaners' AND ABS(`amount` - 24.35) < 1e-31 AND `remarks`='X-Tra Starch'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=20 AND `payee`='Abes Cleaners' AND ABS(`amount` - 10.5) < 1e-31 AND `remarks`='All Dry Clean'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=8 AND `payee`='Cash' AND ABS(`amount` - 60) < 1e-31 AND `remarks`='Trip to Boston'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=21 AND `payee`='Cash' AND ABS(`amount` - 34) < 1e-31 AND `remarks`='Trip to Dayton'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=30 AND `payee`='Local Utilities' AND ABS(`amount` - 87.5) < 1e-31 AND `remarks`='Water'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=31 AND `payee`='Local Utilities' AND ABS(`amount` - 34) < 1e-31 AND `remarks`='Sewer'
LIMIT 1;
DELETE FROM `xxx`
WHERE `checknum`=25 AND `payee`='Joans Gas' AND ABS(`amount` - 15.75) < 1e-31 AND `remarks`='Gas'
LIMIT 1;

select * from `xxx`;
quit

How to repeat:
See description.

Suggested fix:
I don't know if this is a bug, and fixing is presently
beyond me.

This is due to problems with floating point comparisons (4 of your rows do not match the deletion criteria so they are not deleted).  You can read more about problems with floating point here:

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

You would likely prefer using DOUBLE or NUMERIC for this, and doing away with the ABS(amount-?) comparison.

Ok, the problem is that MySQL CC choses too small value of epsilon in the
  ABS(column-value)<epsilon

e.g. for one of the undeleted rows:

mysql> select amount,ABS(amount-245.34),format(amount,30) from xxx where checknum=2;
+--------+---------------------+------------------------------------+
| amount | ABS(amount-245.34)  | format(amount,30)                  |
+--------+---------------------+------------------------------------+
| 245.34 | 3.6621093784106e-06 | 245.339996337890625000000000000000 |
+--------+---------------------+------------------------------------+
1 row in set (0.00 sec)

(with format() you can print the real value of the column, without auto-magical rounding that printf("%f") does internally to pretend the value is what you want it to be)

So in this case it should be at least

ABS(amount-245.34)<4e-6

or

amount=245.339996337890625

I'm recategorizing the report as MySQL CC bug

MySQLCC is not supported anymore and bugs in this software won't be fixed anymore.