Bug #2820 Query returns wrong result
Submitted: 16 Feb 2004 4:17 Modified: 27 Feb 2004 7:22
Reporter: Bjarte Andre Eide Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Embedded Library ( libmysqld ) Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Linux (Kernel 2.4.19))
Assigned to: Dean Ellis CPU Architecture:Any

[16 Feb 2004 4:17] Bjarte Andre Eide
Description:
When comparing a DECIMAL field to a sum(DECIMAL) field, MySQL returnes wrong result when the difference between the fields are very small.

Please see "How to repeat" block for table declaration and example of query returning wrong result.

How to repeat:
The following script will create and insert test data into two tables. 

### Start Script

CREATE TABLE tbl_tmp_ord (ord_id int(11) NOT NULL default '0',
  ord_amount decimal(11,2) NOT NULL default '0.00',
  PRIMARY KEY  (ord_id)
) TYPE=MyISAM;

INSERT INTO tbl_tmp_ord VALUES("1", "6888.03");
INSERT INTO tbl_tmp_ord VALUES("2", "37667.88");
INSERT INTO tbl_tmp_ord VALUES("3", "54448.88");
INSERT INTO tbl_tmp_ord VALUES("4", "31.00");

CREATE TABLE tbl_tmp_ord_inv (
  oinv_ord_id int(11) NOT NULL default '0',
  oinv_lnr int(11) NOT NULL default '0',
  oinv_amount decimal(11,2) NOT NULL default '0.00',
  PRIMARY KEY  (oinv_ord_id,oinv_lnr)
) TYPE=MyISAM;

INSERT INTO tbl_tmp_ord_inv VALUES("1", "1", "7250.56");
INSERT INTO tbl_tmp_ord_inv VALUES("1", "2", "-362.53");
INSERT INTO tbl_tmp_ord_inv VALUES("2", "1", "38499.30");
INSERT INTO tbl_tmp_ord_inv VALUES("2", "2", "-831.42");
INSERT INTO tbl_tmp_ord_inv VALUES("3", "1", "18149.63");
INSERT INTO tbl_tmp_ord_inv VALUES("3", "2", "18149.63");
INSERT INTO tbl_tmp_ord_inv VALUES("3", "3", "18149.62");
INSERT INTO tbl_tmp_ord_inv VALUES("4", "1", "15.18");
INSERT INTO tbl_tmp_ord_inv VALUES("4", "2", "15.82");

### end script

Then execute the following query:
select ord_id, ord_amount, sum(oinv_amount) my_sum from tbl_tmp_ord
inner join tbl_tmp_ord_inv on oinv_ord_id = ord_id
group by 1,2 having ((my_sum - ord_amount) !=  0);

This query should return 0 records, but 3 records are returned.
[27 Feb 2004 7:22] Jani Tolonen
Floating point comparisons are not accurate.

Please see MySQL manual section
A.5.7 Problems with Floating-Point Comparison