MySQL Bugs: #1936: Multiplication error when I do a SELECT

Bug #1936	Multiplication error when I do a SELECT
Submitted:	24 Nov 2003 13:54	Modified:	25 Nov 2003 14:23
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Linux (Linux Debian 3.0r1 stable)
Assigned to:		CPU Architecture:	Any

Description:
To begin, excuse my very bad english :).

When I multiply 2 columns with a SELECT command, the result is wrong.

For example, when I multiply 333333 to 2.22222, the server send to me 740739.24059343, instead of 740739.25926. The problem only appears when I multiply 2 FLOAT columns. There is no problems with DOUBLE columns.

You can test it with the SQL code underneath.

I specify that I don't use the Debian package, I have personally compiled MySQL, but I don't think that's the reason of the problem, because I also use MySQL 4.0.15 binary on Windows XP end the problem also appears.

How to repeat:
CREATE TABLE TestPb (
   col1 FLOAT,
   col2 FLOAT
);

INSERT INTO TestPb VALUES ('333333', '2.22222');

SELECT (col1 * col2) FROM TestPb;

Suggested fix:
I never looked at the source code, but if you give me the name of the file which poses problem, I think that I can correct the mistake.

This is due to the difference in precision between FLOAT and DOUBLE, and is a side-effect of floating point math.  You can view the same behavior with a simple C program that multiplies float and double values.

Thank you.