Bug #74335 Help about FLOOR and DIV disagree with behavior
Submitted: 11 Oct 2014 22:24 Modified: 19 Feb 2016 1:42
Reporter: Programmer Old Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.14-log OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[11 Oct 2014 22:24] Programmer Old
Description:
From the HTML Help:

DIV

Integer division. Similar to FLOOR(), but is safe with BIGINT values.

In MySQL 5.6, if either operand has a noninteger type, the operands are converted to DECIMAL and divided using DECIMAL arithmetic before converting the result to BIGINT. If the result exceeds BIGINT range, an error occurs.

mysql> SELECT 5 DIV 2;
        -> 2

FLOOR((-5) / 2) and (-5) DIV 2 differ

How to repeat:
select 5 div 2, (-5) div 2;

select floor(5 /2 ), floor((-5) / 2);

Suggested fix:
Change either DIV (the better choice) to strict down-rounding, as FLOOR,
or the help-file, that it is 0-centered truncation, not rounding down.
[12 Oct 2014 10:24] Peter Laursen
The result here is correct IMO:

SELECT 5 DIV 2, (-5) DIV 2, FLOOR(5 /2 ), FLOOR((-5) / 2);

/* returns

5 div 2  (-5) div 2  floor(5 /2 )  floor((-5) / 2)  
-------  ----------  ------------  -----------------
      2          -2             2                 -3    
*/

http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html#function_floor says "Returns the largest integer value not greater than X".  (-3) is the the largest integer value not greater than 2.5

However the description in http://dev.mysql.com/doc/refman/5.6/en/arithmetic-functions.html#operator_div is extremely poor.  "similar to" is not an exact term and such "loose term" should not exist in program documentation. Probably it was written without having negative numbers in mind.

I summary I think it is a documentaton issue with http://dev.mysql.com/doc/refman/5.6/en/arithmetic-functions.html#operator_div

-- Peter
-- not a MySQL/Oracle eprson
[12 Oct 2014 10:25] Peter Laursen
corection:

(-3) is the the largest integer value not greater than (-2.5).
[12 Oct 2014 10:56] Peter Laursen
I think a better description would be something like "DIV returns the integer part of a numerical expression (or in other words: the 'quotient' as defined by euclidean division rules in common mathematics)."

(and after that differences with FLOOR may be explained)
[16 Oct 2014 22:57] Programmer Old
Well, you see that "integer part" has no mathematical definition, when you clarify with "quotient", but that is not determinate, either.
In Niven & Zuckerman s "Introduction to the Theory of Numbers" the division algorithm they give this form:
for any two integers N and D, unless D = 0, there are unique such integers q and r that
N = Dq + r (*) and 0 <= r < abs(D) (**).
This is not the integer division found in most processors, and not that reƤlized by DIV, for with this definition q = FLOOR(N/D). In any integer division, it is needful that the equality (*) is scrupulously observed, but for most uses of both quotient and remainder --positive-base-conversion, say, or all solutions to Aj + Bk = C-- any pair q & r for which abs(r) < abs(D) is all right.
But FLOOR has this wonderful feature, its simple relation to the complementary function CEIL(ING):
CEIL(z) = -FLOOR(-z)
. If DIV likewise worked, with the restriction (**) (or maybe 0 <= r < D or 0 >= r > D (***)), then (for positive divisor) one would get FLOOR (with negative divisor CEILING), wherefrom one could get also CEILING (the complementary function). The 0-centered form (abs(r) < abs(D) and NDr >= 0 (****)) is of much narrower use: one cannot, say, directly use it for converting negative bases.

My original goal was CEILING, derived from FLOOR by the double negative.

I hope that DIV does not first convert its operands to integer, and naught is said of that--thereby much good would be lost.

It would be best if DIV worked by, besides the equality (*),
either the restriction (**) (independent of any operand s sign) or (***) dependent on only the divisor s sign,
and not the all too common restriction (****).
[3 Feb 2015 14:28] MySQL Verification Team
I have to agree with Peter Laursen here. Documentation on DIV requires much better explanation on how it works and what results are to be expected.

Fully verified as a documentation bug.
[17 Feb 2016 18:57] Paul DuBois
re: "Well, you see that "integer part" has no mathematical definition"

I think most people understand pretty clearly what "integer part" and "fractional part" mean with respect to numbers such as 12.345.
[17 Feb 2016 18:57] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Seems to be little reason to refer to FLOOR() in the DIV description.

Revised DIV description:

Integer division. Discards from the division result any fractional
part to the right of the decimal point.
[19 Feb 2016 1:42] Programmer Old
This is vague:

Integer division. Discards from the division result any fractional
part to the right of the decimal point.

It is meaningful only under the assumption of a base&magnitude representation. It would be accurate to say that the result is rounded toward zero. But there is yet a point not addressed in the discussion, how the operands are treated ere the division. I get the impression that they are somehow converted to integer. This is important: 5/1.25 = 4, but if the operands are first rounded it becomes 5. And this example: 1.4/.7 = 2, but if the operands are first rounded it is either 1 or NULL, depending on the rounding.
[19 Feb 2016 2:25] Paul DuBois
The description also says (and has for some time, I did not change this):

If either operand has a noninteger type, the operands are converted to DECIMAL and divided using DECIMAL arithmetic before converting the result to BIGINT. If the result exceeds BIGINT range, an error occurs.

(http://dev.mysql.com/doc/refman/5.7/en/arithmetic-functions.html#operator_div)