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-AND ed) & 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?