MySQL Bugs: #6138: MOD operator should not round non-integral argument

Bug #6138	MOD operator should not round non-integral argument
Submitted:	18 Oct 2004 12:48	Modified:	21 Oct 2004 16:56
Reporter:	Joerg Bruehe
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.6	OS:	Any (Any)
Assigned to:	Ramil Kalimullin	Target Version:

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

Speak with Trudy and Joerg if you need nay insight into this bug. -B

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

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

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