|
As the MySQL documentation states, bitwise operators only currently operate on integers.
Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>. (The BIT_AND(), BIT_OR(), and BIT_XOR() functions are aggregate functions described at Section 13.20.1, “Aggregate (GROUP BY) Function Descriptions”.) 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.
http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html#operator_bitwise-and
If a user doesn't understand this, they might be very surprised if they try to use bitwise operators with binary or bit types:
MariaDB [db1]> CREATE OR REPLACE TABLE bin_data (
|
-> col binary(2) NOT NULL default x'00'
|
-> );
|
Query OK, 0 rows affected (0.01 sec)
|
|
MariaDB [db1]> INSERT INTO bin_data VALUES (x'00'), (x'01'), (x'02'), (x'03'), (x'04');
|
Query OK, 5 rows affected (0.00 sec)
|
Records: 5 Duplicates: 0 Warnings: 0
|
|
MariaDB [db1]> SELECT (col & x'01') FROM bin_data;
|
+---------------+
|
| (col & x'01') |
|
+---------------+
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
+---------------+
|
5 rows in set, 10 warnings (0.00 sec)
|
|
MariaDB [db1]> SHOW WARNINGS;
|
+---------+------+-----------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x00\x00' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01\x00' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x02\x00' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x03\x00' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x04\x00' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |
|
+---------+------+-----------------------------------------------+
|
10 rows in set (0.00 sec)
|
This can be very confusing for some users.
MySQL apparently plans to change this in version 8.0:
A planned extension for MySQL 8.0 is to change this cast-to-BIGINT behavior: Bit functions and operators will permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits. Consequently, bit operations on binary arguments in MySQL 5.7 might produce different results in MySQL 8.0. To provide advance notice about this potential change in behavior, the server produces warnings as of MySQL 5.7.11 for bit operations for which binary arguments will not be converted to integer in MySQL 8.0. These warnings afford an opportunity to rewrite affected statements. To explicitly produce MySQL 5.7 behavior in a way that will not change after an upgrade to 8.0, cast bit-operation binary arguments to convert them to integer.
Should we do the same for MariaDB?
|