Description:
When we build one index on a BIT-field, the query use this index may have different results that with the full table scan.
How to repeat:
See the queries below.
```
set sql_mode = '';
CREATE TABLE t5(c1 BIT(16) NOT NULL, c2 BIT(16));
INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118, 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57, 135);
mysql> select c1 from t5 where c1 between '16' and '64';
+--------+
| c1 |
+--------+
| 0x001F |
| 0x003D |
| 0x003B |
| 0x0031 |
| 0x0038 |
| 0x001D |
| 0x0017 |
| 0x0018 |
| 0x0022 |
| 0x001C |
| 0x001E |
| 0x003C |
| 0x002C |
| 0x0039 |
+--------+
```
If we add one index on the column c1, then the query's result is empty.
```
mysql> alter table t5 add key idx(c1);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain format=tree select c1 from t5 where c1 between '16' and '64';
+------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t5.c1 between '16' and '64') (cost=0.46 rows=1)
-> Covering index range scan on t5 using idx (cost=0.46 rows=1)
|
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select c1 from t5 where c1 between '16' and '64';
Empty set (0.69 sec)
```
Description: When we build one index on a BIT-field, the query use this index may have different results that with the full table scan. How to repeat: See the queries below. ``` set sql_mode = ''; CREATE TABLE t5(c1 BIT(16) NOT NULL, c2 BIT(16)); INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118, 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57, 135); mysql> select c1 from t5 where c1 between '16' and '64'; +--------+ | c1 | +--------+ | 0x001F | | 0x003D | | 0x003B | | 0x0031 | | 0x0038 | | 0x001D | | 0x0017 | | 0x0018 | | 0x0022 | | 0x001C | | 0x001E | | 0x003C | | 0x002C | | 0x0039 | +--------+ ``` If we add one index on the column c1, then the query's result is empty. ``` mysql> alter table t5 add key idx(c1); Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain format=tree select c1 from t5 where c1 between '16' and '64'; +------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t5.c1 between '16' and '64') (cost=0.46 rows=1) -> Covering index range scan on t5 using idx (cost=0.46 rows=1) | +------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select c1 from t5 where c1 between '16' and '64'; Empty set (0.69 sec) ```