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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.8, 5.5.31,5.6
• OS: Any
• 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