Bug #78640 CAST OF 'DOUBLE' VALUE TO 'UNSIGNED' GIVES INCORRECT VALUE

• Submitted: 30 Sep 2015 8:42
• Modified: 30 Sep 2015 9:21
• Reporter: Guilhem Bichot
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.5.47, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[30 Sep 2015 8:42] Guilhem Bichot

Description:
The max BIGINT UNSIGNED value:

select convert(-1, unsigned);
|  18446744073709551615 |

cast it to DOUBLE:

select convert(-1, unsigned)+0e0;
|     1.8446744073709552e19 |

so far so good. Now cast this back to BIGINT UNSIGNED:

select convert(convert(-1, unsigned)+0e0,unsigned);
|                         9223372036854775808 |

It's twice too small. Looks like some bit was interpreted as a sign bit?
Same result if -1 is replaced with -2.
More simply:
18000000000000000000e0 is largely under the max number allowed by BIGINT
UNSIGNED, so overflow shouldn't happen, but:
select convert(18000000000000000000e0, unsigned);
|                       9223372036854775807 |
The metadata does say it's "bigint unsigned".

How to repeat:
select convert(convert(-1, unsigned)+0e0,unsigned);
select convert(18000000000000000000e0, unsigned);

[30 Sep 2015 9:21] MySQL Verification Team

Hello Guilhem,

Thank you for the report and test case.

Thanks,
Umesh