bug: when insert bit type date at the edge of the range(63), it returns Empty set

[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 t1 (a BIT(63));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (b'111111111111111');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT HEX(a) FROM t1 WHERE a = b'111111111111111';
+--------+
| HEX(a) |
+--------+
| 7FFF   |
+--------+
1 row in set (0.01 sec)

mysql> SELECT HEX(a) FROM t1 WHERE a = b'111111111111111111111111111111111111111111111111111111111111111';
Empty set (0.00 sec)

Expected behavior

mysql> SELECT HEX(a) FROM t1 WHERE a = b'111111111111111';
+--------+
| HEX(a) |
+--------+
| 7FFF   |
+--------+
1 row in set (0.00 sec)

mysql> SELECT HEX(a) FROM t1 WHERE a = b'111111111111111111111111111111111111111111111111111111111111111';
+------------------+
| HEX(a)           |
+------------------+
| 7FFFFFFFFFFFFFFF |
+------------------+
1 row in set (0.00 sec)

How To Reproduce

drop table t1
CREATE TABLE t1 (a BIT(63));
INSERT INTO t1 VALUES (b'111111111111111');
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111');

SELECT HEX(a) FROM t1 WHERE a = b'111111111111111';
SELECT HEX(a) FROM t1 WHERE a = b'111111111111111111111111111111111111111111111111111111111111111';

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/hustjieke/stonedb.git:feat_support_bit_type_issue919
Branch name: feat_support_bit_type_issue919
Last commit ID: https://github.com/stoneatom/stonedb/commit/38982a599b4f302f63f1612206d8f74d1606098f
Last commit time: Date: Tue Dec 27 08:44:36 2022 +0000
Build time: Date: Thu Dec 29 13:08:25 CST 2022
[root@localhost ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

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

• [ ] Yes, I will!

[RingsC]

related to this #1161

[hustjieke]

ACK.

[hustjieke]

When bit63 used as where condition, the result is empty.

mysql> create table bit63(a bit(63)) engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bit63 values(b'111111111111111111111111111111111111111111111111111111111111111');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bit63 values(b'11111111111111111111111111111111111111111111111111111111111111');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bit63 values(b'1111');
Query OK, 1 row affected (0.00 sec)

mysql> select a+0 from bit63;
+---------------------+
| a+0                 |
+---------------------+
| 9223372036854775807 |
|                  15 |
| 4611686018427387903 |
+---------------------+
3 rows in set (0.00 sec)

mysql> select hex(a) from bit63;
+------------------+
| hex(a)           |
+------------------+
| 7FFFFFFFFFFFFFFF |
| F                |
| 3FFFFFFFFFFFFFFF |
+------------------+
3 rows in set (0.00 sec)

mysql> select hex(a) from bit63 where a=b'1111';
+--------+
| hex(a) |
+--------+
| F      |
+--------+
1 row in set (0.00 sec)

mysql> SELECT HEX(a) FROM bit63 WHERE a = b'111111111111111111111111111111111111111111111111111111111111111';
Empty set (0.00 sec)

mysql> SELECT HEX(a) FROM bit63 WHERE a = b'11111111111111111111111111111111111111111111111111111111111111';
+------------------+
| HEX(a)           |
+------------------+
| 3FFFFFFFFFFFFFFF |
+------------------+
1 row in set (0.00 sec)

explain for bit63:

mysql> explain SELECT HEX(a) FROM bit63 WHERE a = b'111111111111111111111111111111111111111111111111111111111111111';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                                                                               |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | bit63 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where with pushed condition (`test`.`bit63`.`a` = 0x7fffffffffffffff)(t0) Pckrows: 1, susp. 0 (1 empty 0 full). Conditions: 1 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

[hustjieke]

It seems a magic number for bit63(7FFFFFFFFFFFFFFF), I expand bit M = 64, bit64(FFFFFFFFFFFFFFFF) is ok in this case,bit(M), M between 1~62 are all OK.

I need deeper debug to find what happened in where filter.