0

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.

0

1 Answer 1

1

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.

5
  • so do mysql not have bit-types at all, is that just a veneer for an int type? Apr 24, 2018 at 20:01
  • It looks like so
    – mustaccio
    Apr 24, 2018 at 20:01
  • @EvanCarroll you can't say they don't have the bit data type, because they do. Implementation may not be up to your standards, I realize that.
    – mustaccio
    May 2, 2018 at 0:25
  • Can you explain what it means to have a bit type if the operators treat it like an int, and it's stored as an int? May 2, 2018 at 16:34
  • @EvanCarroll 👆
    – mustaccio
    May 2, 2018 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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