bug: POSITION() func process BIT data, return wrong result

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

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

mysql> INSERT INTO bit_table VALUES (B'1101100000000'),(B'110110000000'),(B'11011'),(B'1101');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT POSITION(B'1101' IN b), POSITION(B'11011' IN b),b+0 FROM bit_table;
+------------------------+-------------------------+------+
| POSITION(B'1101' IN b) | POSITION(B'11011' IN b) | b+0  |
+------------------------+-------------------------+------+
|                      0 |                       0 | 6912 |
|                      0 |                       0 | 3456 |
|                      0 |                       0 |   27 |
|                      0 |                       0 |   13 |
+------------------------+-------------------------+------+
4 rows in set (0.00 sec)

Expected behavior

mysql> SELECT POSITION(B'1101' IN b), POSITION(B'11011' IN b),b+0 FROM bit_table;
+------------------------+-------------------------+------+
| POSITION(B'1101' IN b) | POSITION(B'11011' IN b) | b+0  |
+------------------------+-------------------------+------+
|                      0 |                       1 | 6912 |
|                      1 |                       0 | 3456 |
|                      0 |                       2 |   27 |
|                      2 |                       0 |   13 |
+------------------------+-------------------------+------+

How To Reproduce

CREATE TABLE bit_table(b BIT(16));
INSERT INTO bit_table VALUES (B'1101100000000'),(B'110110000000'),(B'11011'),(B'1101');
SELECT POSITION(B'1101' IN b), POSITION(B'11011' IN b),b+0 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!

[davidshiz]

maybe similar to #1219

[RingsC]

1：@hustjieke @davidshiz , pls check this related function family to check wehter these funcitons have same behavior.
2： To @davidshiz , you can verify these function in batch, and open ONE issue to report these in-correct behavior.
At first, you should list all the related function and classify these into some catagories, for example, pos, sub, or bit operation, etc. That will make sure you WILL NOT miss any scenarios.