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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.8, 5.5.47, 5.6.28, 5.7.10 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.