Hex and Binary Literals in MySQL / MariaDB?

Question

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?

Answer 1

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

Answer 2

From Hexadecimal Literals in the MariaDB documentation:

Hexadecimal literals can be written using any of the following syntaxes:

• x'value'
• X'value' (SQL standard)
• 0xvalue (ODBC)

value is a sequence of hexadecimal digits (from 0 to 9 and from A to F). The case of the digits does not matter. With the first two syntaxes, value must consist of an even number of digits. With the last syntax, digits can be odd, and they are treated as if they had an extra 0 at the beginning.

From Cast Functions and Operators in the MySQL 5.1 documentation:

The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be done byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column isn't defined as BINARY or BLOB.