MySQL Bugs: #70732: hex-literals in querys

Bug #70732	hex-literals in querys
Submitted:	26 Oct 2013 14:17	Modified:	1 Nov 2013 10:25
Reporter:	Nick Icanhas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Linux (UBUNTU 12.4)
Assigned to:		CPU Architecture:	Any
Tags:	bit operations, Calculation, hex literals

Description:
Recently I noticed a problem when dealing with hex literals.

According to the manual of MySQL (all versions from 5.0 onwards):

9.1.4. Hexadecimal Literals
“… In numeric contexts, hexadecimal values act like integers (64-bit precision). In string contexts, they act like binary strings, where each pair of hex digits is converted to a character...”

In practice though, all hex-literals used in calculations, functions or bit operations are internally saved as a (presumably) double, thus not preserving the announced 64-bit precision.

For instance:

mysql> SELECT 0xFFFFFFFFFFFFFFFF + 0;
+------------------------+
| 0xFFFFFFFFFFFFFFFF + 0 |
+------------------------+
|   18446744073709552000 |  <-- WRONG
+------------------------+

Now be it a bug or not, from a programmers point of view a 53-bit precision variable is certainly not the expected container for a hex-literal value, especially not when intended to be used for integer calculation or even worse bitwise operations.

Kind Regards

Nick

How to repeat:
Execute a query that performs any mathematical operation on a hex-denoted number between 2^53 (0x20000000000000) and 2^64-1 (0xFFFFFFFFFFFFFFFF)

i.e.

mysql> SELECT 0x20000000000000 + 1;
+----------------------+
| 0x20000000000000 + 1 |
+----------------------+
|     9007199254740992 |
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT 0x20000000000000 + 2;
+----------------------+
| 0x20000000000000 + 2 |
+----------------------+
|     9007199254740994 |
+----------------------+
1 row in set (0.00 sec)

Suggested fix:
In order to preserve consistency and predictability of results, hex and (non floating) decimal literals should be treated equally - with 64 bit precision and issuing a warning on overflow. The representation of hex-literals as characters should be the result of a conversion function (possibly with an optional encoding parameter).

For me making addition operations (including 0) on the largest 64-bit unsigned integer is not a bug.

However, the queries of the type:

SELECT 0x20000000000000 + 1;

and similar are definitely a bug, although not a serious one at all. This is due to the various allowed ways of entering complex character set strings.

CAST() function is, of course, a very simple workaround.

Thanks for the feedback,

I wonder whether you did notice that the highest “64-bit signed integer” does 
not have 64-bit precision when used in mathematical context.

mysql> SELECT 0xFFFFFFFFFFFFFFFF + 0;
+------------------------+
| 0xFFFFFFFFFFFFFFFF + 0 |
+------------------------+
|   18446744073709552000 |
+------------------------+

mysql> SELECT CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED);
+--------------------------------------+
| CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) |
+--------------------------------------+
|                 18446744073709551615 |
+--------------------------------------+

According to the manual and to your own interpretation, it should come with 
64-bit precision, but it doesn't. 

Of course the significance for this bug is low, if you assume that people will 
do maths with big integers using hex-literals. (why should they in the first 
place ?).  

I was more concerned about operations on the bit level when some calculation is 
involved as well.

Take for instance some arbitrary hex-literal like 0x0FFFFFFFFFFFFFFE. 

Here we see instantly that the first four and the last bit are not set. 
Now some bold programmer might come to mind to switch the last bit by adding one 
(1) to the hex-literal and as he also wants the second four bits to be unset he 
decides to to something like this:

 SELECT (0x0FFFFFFFFFFFFFFE + 1) >> 4

Of course the right operator would be a bit OR ( | ) instead of the + but this 
involves pressing two keys and we know how lazy programmers tend to be. 

So he gets:

mysql> SELECT (0x0FFFFFFFFFFFFFFE + 1) >> 4;
+-------------------------------+
| (0x0FFFFFFFFFFFFFFE + 1) >> 4 |
+-------------------------------+
|             72057594037927936 |
+-------------------------------+

this is a small difference in decimal notation (72057594037927935 is correct) 
but a very huge one if you look at the bits: 0x00FFFFFFFFFFFFFF  vs.  0x100000000000000

Now as only few people use hex-literals for calculation there might be quite a 
few that use them for bit operations. A container with 64 different yes/no 
values comes can be quite handy at times. If you can even see which bits are 
set, like this is the case with hex notation (some experience assumed) than the 
most natural way is to use hex-literals.

Of course the CAST function is a possible workaround but the problem is, that 
you would never use it intuitively before you encounter the problem above. 
Moreover by the way the conversion from hex string to number is implemented, it 
yields, more often than not, correct results, thus misleading the programmers 
into thinking, that in numeric context the conversion will be to a 64-bit 
integer and even the manual encourages this belief.

Btw.,  a faster workaround than the cast to unsigned is:

mysql> SELECT 0xFFFFFFFFFFFFFFFF >> 0;
+-------------------------+
| 0xFFFFFFFFFFFFFFFF >> 0 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+	

This all said, I think that there is a potential to break real world 
applications and that the significance of the bug is much higher than 
“not serious at all”.

FWIW,  current trunk tells us :

mysql> SELECT 0xFFFFFFFFFFFFFFFF + 0;
+------------------------+
| 0xFFFFFFFFFFFFFFFF + 0 |
+------------------------+
|   18446744073709551615 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT 0x20000000000000 + 1;
+----------------------+
| 0x20000000000000 + 1 |
+----------------------+
|     9007199254740993 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT 0x20000000000000 + 2;
+----------------------+
| 0x20000000000000 + 2 |
+----------------------+
|     9007199254740994 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT 0x20000000000000 + 1;
+----------------------+
| 0x20000000000000 + 1 |
+----------------------+
|     9007199254740993 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT 0xFFFFFFFFFFFFFFFF + 0;
+------------------------+
| 0xFFFFFFFFFFFFFFFF + 0 |
+------------------------+
|   18446744073709551615 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED);
+--------------------------------------+
| CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) |
+--------------------------------------+
|                 18446744073709551615 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (0x0FFFFFFFFFFFFFFE + 1) >> 4
    -> ;
+-------------------------------+
| (0x0FFFFFFFFFFFFFFE + 1) >> 4 |
+-------------------------------+
|             72057594037927935 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (0x0FFFFFFFFFFFFFFE + 1) >> 4;
+-------------------------------+
| (0x0FFFFFFFFFFFFFFE + 1) >> 4 |
+-------------------------------+
|             72057594037927935 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 0xFFFFFFFFFFFFFFFF >> 0;
+-------------------------+
| 0xFFFFFFFFFFFFFFFF >> 0 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)