Bug #6138 MOD operator should not round non-integral argument
Submitted: 18 Oct 2004 10:48 Modified: 21 Oct 2004 14:56
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.6 OS:Any (Any)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[18 Oct 2004 10:48] Joerg Bruehe
Description:
The "MOD" operator accepts non-integral values as its first operand, but rounds them to integer when calculating the result.

Accepting them is an extension of the SQL standard, but then the result should not be rounded but be as exact as possible - or the operation should be rejected with an error, best based on the type and not on the value.

The manual does not specify the current behaviour, but just refers to the C '%' operator for the definition. This is incorrect, because in C a modulo operation is only allowed on integral types, and there is no implicit rounding.

How to repeat:
mysql> select 4.49 mod 3 ;
--------------
select 4.49 mod 3
--------------

4.49 mod 3
1
mysql> select 4.5 mod 3 ;
--------------
select 4.5 mod 3
--------------

4.5 mod 3
2
[19 Oct 2004 16:14] Brian Aker
Speak with Trudy and Joerg if you need nay insight into this bug. -B
[19 Oct 2004 16:23] Trudy Pelzer
Some notes on the behaviour we want:

MOD should work on any numeric data type, not just on integers (exact numeric
with a scale of zero). MySQL currently does this. When this bug is fixed, we'll have to ask Paul DuBois to clarify this in the Reference Manual.

The result of MOD should be the exact calculation, rather than a rounded or
truncated result, as we are currently doing. The MOD calculation should be:
MOD(x,y) = x - FLOOR(x/y) * y
Thus:
MOD(1197.90,50) = 1197.90 - FLOOR(1197.90/50) * 50
                = 1197.90 - FLOOR(23.958) * 50
                = 1197.90 - (23*50)
                = 1197.90 - 1150
                = 47.90
MySQL currently returns 48, but should return 47.9 (or 47.90).
[21 Oct 2004 14:07] Sergei Golubchik
MOD(x,y) = x - FLOOR(x/y) * y

is of course only valid for y>0

Otherwise it should be

MOD(x,y) = x - FLOOR(x / (ABS(y))* ABS(y))
[21 Oct 2004 14:56] Ramil Kalimullin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html