Bug #72127 DIV with floats or doubles and unsigned integers sometimes errors out spuriously
Submitted: 25 Mar 2014 19:16 Modified: 18 Nov 2019 22:42
Reporter: Arthur O'Dwyer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.8, 5.5.31,5.6 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 2014 19:16] Arthur O'Dwyer
Description:
Bug 44363 is related (but only insofar as that DIV for floats and doubles is broken in many ways).

If you call DIV between a floating-point value and an unsigned int, and the intermediate result of the (non-integer) division is negative, then you get an ERROR 1690 "out of range" even if the rounded result would have been zero.

    SELECT -1e0 DIV cast(42 as unsigned) x;

        ERROR 1690 (22003): BIGINT UNSIGNED value is out of range
        in '(-(10e-1) DIV cast(42 as unsigned))'

EXCEPT, if the first operand is strictly smaller than 1, you DON'T get that error after all.

    SELECT -9e-1 DIV cast(42 as unsigned) x;

        +------+
        | x    |
        +------+
        |    0 |
        +------+

How to repeat:
SELECT -1e0 DIV cast(42 as unsigned) x;
SELECT -9e-1 DIV cast(42 as unsigned) x;

Suggested fix:
Obviously the rounded result should be computed correctly instead of erroring out; but I haven't tried to come up with a patch myself.
[26 Mar 2014 21:06] MySQL Verification Team
Thank you for the bug report.
[18 Nov 2019 22:42] Roy Lyseng
Posted by developer:
 
Fixed in 5.7