bug: Extract a SUBSTRING from BIT type data, return incorrect rusult set

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> CREATE TABLE bit_table(b BIT(11));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO bit_table VALUES (B'10');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO bit_table VALUES (B'00000000000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO bit_table VALUES (B'11011000000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO bit_table VALUES (B'01010101010');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT b+0,SUBSTRING(b FROM 2 FOR 4)+0 AS sub_2_4 FROM bit_table;
+------+---------+
| b+0  | sub_2_4 |
+------+---------+
|    2 |       0 |
|    0 |       0 |
| 1728 |     728 |
|  682 |      82 |
+------+---------+
4 rows in set (0.00 sec)

Expected behavior

mysql> SELECT b+0,SUBSTRING(b FROM 2 FOR 4)+0 AS sub_2_4 FROM bit_table;
+------+---------+
| b+0  | sub_2_4 |
+------+---------+
|    2 |       0 |
|    0 |       0 |
| 1728 |       0 |
|  682 |       0 |
+------+---------+
4 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE bit_table(b BIT(11));
INSERT INTO bit_table VALUES (B'10');
INSERT INTO bit_table VALUES (B'00000000000');
INSERT INTO bit_table VALUES (B'11011000000');
INSERT INTO bit_table VALUES (B'01010101010');
SELECT b+0,SUBSTRING(b FROM 2 FOR 4)+0 AS sub_2_4 FROM bit_table;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 1fa9d3346
        Last commit time: Date:   Fri Jan 13 07:03:20 2023 +0000
        Build time: Date: Fri Jan 13 17:22:15 CST 2023
root@ub01:~# cat /etc/issue
Ubuntu 20.04.5 LTS \n \l

Are you interested in submitting a PR to solve the problem?

• Yes, I will!