function bit_count result is Inconsistent with MySQL

[fzzf678]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

In TiDB, the result is 0, but in MySQL is 13:

mysql> set @a = b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001';
Query OK, 0 rows affected (0.00 sec)

mysql> select bit_count(@a);
+---------------+
| bit_count(@a) |
+---------------+
|             0 |
+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '"@��@  '   |
+---------+------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

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

In MySQL:

mysql> set @a = b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001';
Query OK, 0 rows affected (0.00 sec)

mysql> select bit_count(@a);
+---------------+
| bit_count(@a) |
+---------------+
|            13 |
+---------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[fzzf678]

With out user define var, the results are also different
tidb:

mysql> select bit_count(b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001');
+-----------------------------------------------------------------------------------------------------------+
| bit_count(b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001') |
+-----------------------------------------------------------------------------------------------------------+
|                                                                                                        64 |
+-----------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql:

mysql> select bit_count(b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001');
+-----------------------------------------------------------------------------------------------------------+
| bit_count(b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001') |
+-----------------------------------------------------------------------------------------------------------+
|                                                                                                         0 |
+-----------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

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

[Defined2014]

The result of TiDB is same as MySQL 5.7
And from mysql manual, the bit_count function has different result between 5.7 and 8.0

https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html#bit-operations-5-7

[bb7133]

Ref #30637