[MDEV-6092] Weird hex warnings Created: 2014-04-14  Updated: 2018-01-09  Resolved: 2014-04-24

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.37, 10.0.10
Fix Version/s: 5.5.38, 10.0.11

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Ian Gilfillan
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5770 Inconsistency between string and hex ... Open

 Description   

The first syntax is ok, the latter 2 issue warnings:

MariaDB [(none)]> \W
 
Show warnings enabled.
 
MariaDB [(none)]> SELECT CAST(0xAA AS UNSIGNED), CAST(x'aa' AS UNSIGNED), CAST(X'aa' AS UNSIGNED);
 
+------------------------+-------------------------+-------------------------+
 
| CAST(0xAA AS UNSIGNED) | CAST(x'aa' AS UNSIGNED) | CAST(X'aa' AS UNSIGNED) |
 
+------------------------+-------------------------+-------------------------+
 
|                    170 |                       0 |                       0 |
 
+------------------------+-------------------------+-------------------------+
 
1 row in set, 2 warnings (0.00 sec)
 
 
 
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
 
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'

But based on MySQL docs, these syntaxes should be equivalent:
https://dev.mysql.com/doc/refman/5.6/en/hexadecimal-literals.html

Sorry, I didn't try this on MySQL or on older Maria versions.

 Comments   
Comment by Elena Stepanova [ 2014-04-14 ]

Not reproducible on 5.1-5.3, MySQL 5.5 / 5.6.

It might be the same as MDEV-5770 or related to it.

Comment by Alexander Barkov [ 2014-04-24 ]

This is not a bug.

0xHHHH is a MySQL/MariaDB extension. It can work as a number or as a string depending on context.
In case of "CAST(0xAA AS UNSIGNED)" it works as a number.

X'HHHH' is a standard SQL syntax for binary string literals.
Previously it erroneously worked in the same way with 0xHHHH,
but in 5.5.31 it was intentionally changed to behave as a string in all contexts (and never as a number).

The change was done:

  • to fix the problem reported in MDEV-4489
  • to be more SQL standard

So now "CAST(0xHHHH AS UNSIGNED)" and "CAST(0x'HHHH' AS UNSIGNED)" work differently.
For example:

  • "CAST(0x31 AS UNSIGNED)" treats the argument as a number, which is decimal 49.
  • "CAST(X'31' AS UNSIGNED" treats the argument as a string,
    which consists of the character "U+0031 DIGIT ONE", and which is further converted to the number 1.

In the reported example:

  • "CAST(0xAA AS UNSIGNED)" treats the argument as a decimal number 170.
  • "CAST(X'AA' AS UNSIGNED)" treats the argument as a string,
    then fails to find any digits in the string, hence returns 0 with a warning.

Ian, please consider documenting this difference in
https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/
or any other relevant sections.
Thanks.

Comment by Ian Gilfillan [ 2014-04-24 ]

This has now been documented at https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/ and https://mariadb.com/kb/en/cast/

Generated at Thu Feb 08 07:09:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.