Bug #78641 cast of too big HEX literal to BIGINT UNSIGNED: bad result and no warning
Submitted: 30 Sep 2015 8:47 Modified: 15 Oct 2015 14:01
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.8, 5.7.10, 5.6.28 OS:Any
Assigned to: CPU Architecture:Any

[30 Sep 2015 8:47] Guilhem Bichot
Description:
This is correct (truncation to max value of type *with warning*):

select convert(9999999999999999999999999999999999999999999,unsigned);
|                                          18446744073709551615 |
Warning (Code 1292): Truncated incorrect DECIMAL value: '9999999999999999999999999999999999999999999'

So is this:
select convert('9999999999999999999999999999999999999999999',unsigned);
|                                            18446744073709551615 |
Warning (Code 1292): Truncated incorrect INTEGER value: '9999999999999999999999999999999999999999999'

This is incorrect:
select convert(0x9999999999999999999999999999999999999999999,unsigned);
|                                            11068046444225730969 |
Only some parts of the hex string are converted, and there's no warning.

How to repeat:
select convert(0x9999999999999999999999999999999999999999999,unsigned);

Other examples to fix:
select 9999999999999999999999999999999999999999999 | 0;
gives max value + warning (good), but
select 0x9999999999999999999999999999999999999999999 | 0;
gives 11068046444225730969 and no warning (bad).

Suggested fix:
Here:
longlong Item_hex_string::val_int()
{
  // following assert is redundant, because fixed=1 assigned in constructor
  DBUG_ASSERT(fixed == 1);
  char *end=(char*) str_value.ptr()+str_value.length(),
       *ptr= end - min<size_t>(str_value.length(), sizeof(longlong));

it just takes the last 8 bytes; if by doing this it ignores any non-zero byte elsewhere in the string, it should emit a warning and return the max possible bigint value.
[30 Sep 2015 8:49] Guilhem Bichot
select 0x9999999999999999999999999999999999999999999 + 0;
|                              11068046444225730000 |
must also be fixed.
[30 Sep 2015 8:52] Guilhem Bichot
moreover
0x9999999999999999999999999999999999999999999 + 0
is of type DOUBLE, I rather expected it would be BIGINT, as the doc says
"In numeric contexts, hexadecimal values act like integers (64-bit precision)".
[30 Sep 2015 8:54] Umesh Shastry
Hello Guilhem,

Thank you for the report and test case.

Thanks,
Umesh
[12 Oct 2015 8:30] Guilhem Bichot
the "type DOUBLE" issue above is the cause of
http://bugs.mysql.com/bug.php?id=49530
which has been marked as duplicate.
[15 Oct 2015 14:01] Paul Dubois
Noted in 5.7.10, 5.8.0 changelogs.

Casting large hexadecimal values could produce an incorrect result
and no truncation warning.