Bug #78828 HEX() function converts DECIMAL to DOUBLE and thus returns wrong result

• Submitted: 14 Oct 2015 8:28
• Modified: 21 Jan 2016 22:59
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.5.44, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[14 Oct 2015 8:28] Guilhem Bichot

Description:
+select hex(cast(9007199254740992 as decimal(30,0)));
+hex(cast(9007199254740992 as decimal(30,0)))
[phone]

+select hex(cast(9007199254740993 as decimal(30,0)));
+hex(cast(9007199254740993 as decimal(30,0)))
[phone]

+select hex(cast(9007199254740994 as decimal(30,0)));
+hex(cast(9007199254740994 as decimal(30,0)))
[phone]

The middle one is a bad result:
- 9007199254740993 is in the range of BIGINT (signed and unsigned)
- so the DECIMAL should be converted, by HEX(), to BIGINT without loss of precision, and then to hexadecimal symbols.
Here there is a loss of precision as ...92 and ...93 give same result.

When fixing, please also test
select hex(cast(0x20000000000000 as unsigned) + 1);
select hex(cast(0x20000000000000 as decimal(30,0)) + 1);
select hex(cast(0x20000000000000 as decimal(30,0)) + 2);

How to repeat:
select hex(cast(9007199254740992 as decimal(30,0)));
select hex(cast(9007199254740993 as decimal(30,0)));
select hex(cast(9007199254740994 as decimal(30,0)));

[21 Jan 2016 22:59] Paul DuBois

Noted in 5.8.0 changelog.

Handling by the HEX() function of numbers larger than 2**64 was
improved.