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.
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.