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

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