MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

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 12.20.1, “Aggregate 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.

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.
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 are not 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.

The five problematic expression types to watch out for are:

{ & | ^ }`nonliteral_binary`

`binary`

{ & | ^ }`binary`

`nonliteral_binary`

{ << >> }`nonliteral_binary`

~`anything`

`nonliteral_binary`

(`AGGR_BIT_FUNC`

)`nonliteral_binary`

Those expressions return `BIGINT`

in
MySQL 5.7, binary string in 8.0.

Explanation of notation:

`{`

: List of operators that apply to the given expression type.`op1`

... }`op2`

: Any kind of binary string argument, including a hexadecimal literal, bit literal, or`binary`

`NULL`

literal.: An argument that is a binary string value other than a hexadecimal literal, bit literal, or`nonliteral_binary`

`NULL`

literal.: An aggregate function that takes bit-value arguments:`AGGR_BIT_FUNC`

`BIT_AND()`

,`BIT_OR()`

,`BIT_XOR()`

.

The server produces a single warning for each problematic expression in a statement, not a warning for each row processed. Suppose that a statement containing two problematic expressions selects three rows from a table. The number of warnings per statement execution is two, not six. The following example illustrates this.

mysql>Query OK, 0 rows affected (0.03 sec) mysql>`CREATE TABLE t(vbin1 VARBINARY(32), vbin2 VARBINARY(32));`

Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>`INSERT INTO t VALUES (3,1), (3,2), (3,3);`

->`SELECT HEX(vbin1 & vbin2) AS op1,`

->`HEX(vbin1 | vbin2) AS op2`

+------+------+ | op1 | op2 | +------+------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | +------+------+ 3 rows in set, 2 warnings (0.00 sec) mysql>`FROM t;`

*************************** 1. row *************************** Level: Warning Code: 1287 Message: Bitwise operations on BINARY will change behavior in a future version, check the 'Bit functions' section in the manual. *************************** 2. row *************************** Level: Warning Code: 1287 Message: Bitwise operations on BINARY will change behavior in a future version, check the 'Bit functions' section in the manual. 2 rows in set (0.00 sec)`SHOW WARNINGS\G`

To avoid having an affected statement produce a different result
after an upgrade to MySQL 8.0, rewrite it so that it generates no
bit-operation warnings. To do this, cast at least one binary
argument to `BIGINT`

with
`CAST(... AS UNSIGNED)`

. This makes
the MySQL 5.7 implicit binary-to-integer cast explicit:

mysql>->`SELECT HEX(CAST(vbin1 AS UNSIGNED) & CAST(vbin2 AS UNSIGNED)) AS op1,`

->`HEX(CAST(vbin1 AS UNSIGNED) | CAST(vbin2 AS UNSIGNED)) AS op2`

+------+------+ | op1 | op2 | +------+------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | +------+------+ 3 rows in set (0.01 sec) mysql>`FROM t;`

Empty set (0.00 sec)`SHOW WARNINGS\G`

With the statement rewritten as shown, MySQL 8.0 respects the intention to treat the binary arguments as integers and produces the same result as in 5.7. Also, replicating the statement from MySQL 5.7 to 8.0 does not produce different results on different servers.

An affected statement that cannot be rewritten is subject to these potential problems with respect to upgrades and replication:

The statement might return a different result after an upgrade to MySQL 8.0.

Replication to MySQL 8.0 from older versions might fail for statement-based and mixed-format binary logging. This is also true for replaying older binary logs on an 8.0 server (for example, using

**mysqlbinlog**). To avoid this, switch to row-based binary logging on the older master server.

The following list describes available bit functions and operators:

Bitwise OR.

The result is an unsigned 64-bit integer.

mysql>

-> 31`SELECT 29 | 15;`

Bitwise AND.

The result is an unsigned 64-bit integer.

mysql>

-> 13`SELECT 29 & 15;`

Bitwise XOR.

The result is an unsigned 64-bit integer.

mysql>

-> 0 mysql>`SELECT 1 ^ 1;`

-> 1 mysql>`SELECT 1 ^ 0;`

-> 8`SELECT 11 ^ 3;`

Shifts a longlong (

`BIGINT`

) number to the left.The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.

mysql>

-> 4`SELECT 1 << 2;`

Shifts a longlong (

`BIGINT`

) number to the right.The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.

mysql>

-> 1`SELECT 4 >> 2;`

Invert all bits.

The result is an unsigned 64-bit integer.

mysql>

-> 4`SELECT 5 & ~1;`

Returns the number of bits that are set in the argument

as an unsigned 64-bit integer, or`N`

`NULL`

if the argument is`NULL`

.mysql>

-> 4, 3`SELECT BIT_COUNT(29), BIT_COUNT(b'101010');`