bitwise operations with binary args behavior changes by mysql 5.7 and 8.0 both

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
Select  HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
Select  HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0);
show warnings;

2. What did you expect to see? (Required)

Select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------------------------------------------+
| HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------------------------------------------+
| FFFFFFFFFFFFFFFF                                                                       |
+----------------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------------------------------------------+
| HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------------------------------------------+
| 0                                                                                      |
+----------------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> Select  HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------+
| HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------+
| FFFFFFFFFFFFFFFF                                   |
+----------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> Select  HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0);
+----------------------------------------------------+
| HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0) |
+----------------------------------------------------+
| FFFFFFFFFFFFFFFF                                   |
+----------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Bitwise operations on BINARY will change behavior in a future version, check the 'Bit functions' section in the manual. |
| Warning | 1292 | Truncated incorrect INTEGER value: '\x01\x9C\x9B\xBC\xCE\x9E\x0A\x88\xF5!%r\xB0\xC5\xB9\xE6\xD0'                        |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> Select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------------------------------------------+
| HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------------------------------------------+
| 0                                                                                      |
+----------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select  HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------------------------------------------+
| HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------------------------------------------+
| FFFFFFFFFFFFFFFF                                                                       |
+----------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> Select  HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
+----------------------------------------------------+
| HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2) |
+----------------------------------------------------+
| 0                                                  |
+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> Select  HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0);
+----------------------------------------------------+
| HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0) |
+----------------------------------------------------+
| 0                                                  |
+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect BINARY value: '0x019c9bbcce9e0a88f5212572b0c5b9e6d0' |
+---------+------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-184-g51f53f337-dirty
Edition: Community
Git Commit Hash: 51f53f3376bd548b302f654d6c11096f89119145
Git Branch: master
UTC Build Time: 2021-11-23 02:51:51
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[Alkaagr81]

drop table if exists t;
 CREATE TABLE t(i INT, li BIGINT, b VARBINARY(8), lb VARBINARY(24), d DOUBLE GENERATED ALWAYS AS (i*1.0));
 INSERT INTO t(i, li, b, lb) VALUES (0, 0, X'0000000000000000', X'000000000000000000000000000000000000000000000000');
 INSERT INTO t(i, li, b, lb) VALUES (10, 10, x'0A', x'00000000000000000000000000000000000000000000000A');
 SELECT HEX(BIT_AND(b) OVER w), SUM(d) OVER w AS dsum FROM t WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);

IN MYSQL

mysql>  SELECT HEX(BIT_AND(b) OVER w), SUM(d) OVER w AS dsum FROM t WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
ERROR 3513 (HY000): Binary operands of bitwise operators must be of equal length

IN TIDB

mysql>  SELECT HEX(BIT_AND(b) OVER w), SUM(d) OVER w AS dsum FROM t WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
+------------------------+------+
| HEX(BIT_AND(b) OVER w) | dsum |
+------------------------+------+
| 0                      |    0 |
| 0                      |   10 |
+------------------------+------+
2 rows in set, 3 warnings (0.00 sec)