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,

  SELECT b'00000001' AS value, b'00000001' = 0 AS isequal;

SELECT value, value=0, isequal AS wasequal
| value | value=0 | wasequal |
|      |       1 |        0 |

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


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 |
  • Could you explain what & does on strings? And where does the SQL Standard state that Binary Strings behave as "strings" in all contexts? The spec calls them "binary strings" but makes it clear that they're a different type. The spec also does not define bitwise operators (afaik). – Evan Carroll Apr 24 '18 at 17:21
  • also, these are supposed to be their own Bit-Value type: dev.mysql.com/doc/refman/8.0/en/bit-type.html. So if x'55' is a string, and 0x55 is an int. What is a Bit-Value type? – Evan Carroll Apr 24 '18 at 17:24

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.