MariaDB are bitwise operators defined on strings

bit manipulationmariadboperatorstring

Is the behavior of BITAND, (bitwise and) defined in MariaDB over strings?

SELECT '0011' & '0011', 'ff' & 'ff', 'yy' & 'YY', '5' & '5';
+-----------------+-------------+-------------+-----------+
| '0011' & '0011' | 'ff' & 'ff' | 'yy' & 'YY' | '5' & '5' |
+-----------------+-------------+-------------+-----------+
|              11 |           0 |           0 |         5 |
+-----------------+-------------+-------------+-----------+

I'm just wondering what is exactly happening here.

Best Answer

It appears that MariaDB (and MySQL for that matter) attempts to implicitly cast operands of bitwise operators to integers, and it is known MySQL behaviour when converting strings to integers to ignore the rest of the string if it encounters non-digits, e.g. cast('123abc' as integer) will yield integer 123, and cast('ff' as integer) will yield 0.

This is a feature of the bitwise operators and bit functions, not the BIT type itself, as stated in the MySQL manual (I assume MariaDB inherits this behaviour):

Currently, bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types are converted to BIGINT and truncation might occur.

It looks like this behaviour is going to change in MySQL version 8:

An extension for MySQL 8.0 changes this cast-to-BIGINT behavior: Bit functions and operators permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits.

which will bring it in line with your expectations.

Related Question