Hex and Binary Literals in MySQL / MariaDB?

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 nu

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 |
+--------------------+-------------+---------------------------+

StackExchange Database Administrators Q#204747, answer score: 2