Mysql – Hex and Binary Literals in MySQL / MariaDB

bit manipulationmariadbmariadb-10.1MySQL

Another question I have pertains to just the & over string types. This question is over the binary-string literals as constructed with B'' or X''.

MariaDB supports binary and hex literals,

SELECT X'01', B'00000001', X'01' = B'00000001';
| X'01' | B'00000001' | X'01' = B'00000001' |
+-------+-------------+---------------------+
|    X  |       X     |                   1 |

* Using `X` because StackExchange strips the non-printable character.

However, interestingly, they're not the same when I pass them through the Binary-operators

SELECT X'01' & B'00000001', X'01' & X'01', B'00000001' & B'00000001';
| X'01' & B'00000001' | X'01' & X'01' | B'00000001' & B'00000001' |
+---------------------+---------------+---------------------------+
|                   0 |             0 |                         1 |

So if they're treated as bit-strings it doesn't make sense (because the result should be 1. And if they're treated as strings it doesn't make sense because the actual string is in fact (BIT-ANDed) & to 1. This shows they don't "behave as a string in all contexts".

SELECT X'01' & B'00000001', '01' & '00000001';
+---------------------+-------------------+
| X'01' & B'00000001' | '01' & '00000001' |
+---------------------+-------------------+
|                   0 |                 1 |
+---------------------+-------------------+

I see this, in the doc on hex literals,

For hexadecimal literals, bit operations are considered numeric context, but bit operations permit numeric or binary string arguments in MySQL 8.0 and higher. To explicitly specify binary string context for hexadecimal literals, use a _binary introducer for at least one of the arguments.

MariaDB says,

Normally, hexadecimal literals are interpreted as binary string, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number.

But that still doesn't seem to cut it, if anything it makes it worse (now none of the results of & return non-0.

SELECT _binary X'01' & _binary B'00000001', _binary X'01' & _binary X'01', _binary B'00000001' & _binary B'00000001';
| _binary X'01' & _binary B'00000001' | _binary X'01' & _binary X'01' | _binary B'00000001' & _binary B'00000001' |
+-------------------------------------+-------------------------------+-------------------------------------------+
|                                   0 |                             0 |                                         0 |

Even an explicit cast is wrong,

SELECT CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8));
| CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8)) |
+--------------------------------------------------------------------------+
|                                                                        0 |

Another bizarre thing is that they act different when stored on a table, for example,

CREATE TABLE g
AS
  SELECT b'00000001' AS value, b'00000001' = 0 AS isequal;

SELECT value, value=0, isequal AS wasequal
FROM g;
+-------+---------+----------+
| value | value=0 | wasequal |
+-------+---------+----------+
|      |       1 |        0 |
+-------+---------+----------+

Why do binary literals and hex-literals behave different and what are their behaviors?

Best Answer

This is expected behaviour in MariaDB since versions 10.0.3 and 5.5.31. It looks like you're running the queries in MariaDB, but reading the MySQL docs. These are similar, but diverging RDBMSes.

According to the MariaDB documentation on hexadecimal literals:

The first two syntaxes; X'value' and x'value, follow the SQL standard, and behave as a string in all contexts in MariaDB since MariaDB 10.0.3 and MariaDB 5.5.31 (fixing MDEV-4489). The latter syntax, 0xvalue, is a MySQL/MariaDB extension for hex hybrids and behaves as a string or as a number depending on context. MySQL treats all syntaxes the same, so there may be different results in MariaDB and MySQL (see below).

The queries in your example will give warnings such as:

| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |

So you can get your desired behaviour by using the non-standard 0xvalue syntax:

SELECT 0x01 & B'00000001', 0x01 & 0x01, B'00000001' & B'00000001';

Which gives:

+--------------------+-------------+---------------------------+
| 0x01 & B'00000001' | 0x01 & 0x01 | B'00000001' & B'00000001' |
+--------------------+-------------+---------------------------+
|                  1 |           1 |                         1 |
+--------------------+-------------+---------------------------+
Related Question