Bug #30776 MOD function produces incorrect results with string arguments

• Submitted: 3 Sep 2007 23:13
• Modified: 6 Dec 2007 16:36
• Reporter: Lee Nelson
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 4.1, 5.0, 5.1
• OS: Linux (debian 4.0)
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[3 Sep 2007 23:13] Lee Nelson

Description:
When given a number formatted as a string, the MOD function producess incorrect results.

When using Perl DBI with "bind values", all bound values are inserted into the SQL statement as strings, making the MOD function unusable with bind values. 

How to repeat:
select MOD(491, 49.1)
0.0  -- correct result         

select MOD(491, '49.1');
49.1 -- incorrect result

Suggested fix:
I don't know how to fix this.

[3 Sep 2007 23:15] Lee Nelson

changed severity to serious

[4 Sep 2007 0:34] Hartmut Holzgraefe

verified, see uploaded test case

works fine for integer values, for floats it fails
as soon either of the parameters is a string

[6 Dec 2007 16:36] Evgeny Potemkin

The first query treats its arguments as DECIMALs and as exact numbers.
The second query converts string to a FLOAT (this is default for strings), and a FLOAT constant can store only an approximate value, not the exact (see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html). Due to this rounding error occur during evaluation and returned value isn't exactly 49.1, but an approximate of it.
To get the expected result you should use this expression:
  MOD(491, CAST('49.1' AS DECIMAL(3.1))