Details
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.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-5770 Inconsistency between string and hex warning representation when converting from string to the other types
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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 https://mariadb.atlassian.net/browse/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.
This has now been documented at https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/ and https://mariadb.com/kb/en/cast/
Not reproducible on 5.1-5.3, MySQL 5.5 / 5.6.
It might be the same as MDEV-5770 or related to it.