Description:
When executing the same query on a table with a column of BIT type, the results are different with different execution plans.
How to repeat:
CREATE TABLE t1 (
`id` varchar(36),
`status` bit(1),
`store_id` varchar(20),
PRIMARY KEY (`id`),
KEY `index_tenant` (`store_id`, `status`)
);
INSERT INTO t1 VALUES ('7B03CF04', b'1', 'h09az');
root@localhost:test 8.0.23> SELECT * FROM t1 WHERE status = '1';
Empty set (0.00 sec)
root@localhost:test 8.0.23> EXPLAIN SELECT * FROM t1 WHERE status = '1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
root@localhost:test 8.0.23> SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE status = '1';
+----------+----------------+----------+
| id | status | store_id |
+----------+----------------+----------+
| 7B03CF04 | 0x01 | h09az |
+----------+----------------+----------+
1 row in set (0.00 sec)
root@localhost:test 8.0.23> EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE status = '1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Suggested fix:
The root cause is when we don't force the optimizer to use primary index, the optimizer considers RANGE scan and tries to set up a min-max tree in test_quick_select() -> get_mm_tree(). In this process, the optimizer regards '1' as a string and saves its ASCII code into the field of type BIT(1). Of course, the value overflows and the optimizer directly returns the following trace:
"setup_range_conditions": [
{
"impossible_condition": {
"cause": "value_out_of_range"
}
}
],
"memory_capacity_exceeded": false,
"impossible_range": true
With the second query using primary scan, the comparator for the where condition is set up to regard both sides as real types in Arg_comparator::set_cmp_func(). So the executor regards '1' as integer 1, which equals to the decimal representation of bit b'1'.
Description: When executing the same query on a table with a column of BIT type, the results are different with different execution plans. How to repeat: CREATE TABLE t1 ( `id` varchar(36), `status` bit(1), `store_id` varchar(20), PRIMARY KEY (`id`), KEY `index_tenant` (`store_id`, `status`) ); INSERT INTO t1 VALUES ('7B03CF04', b'1', 'h09az'); root@localhost:test 8.0.23> SELECT * FROM t1 WHERE status = '1'; Empty set (0.00 sec) root@localhost:test 8.0.23> EXPLAIN SELECT * FROM t1 WHERE status = '1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ 1 row in set, 1 warning (0.00 sec) root@localhost:test 8.0.23> SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE status = '1'; +----------+----------------+----------+ | id | status | store_id | +----------+----------------+----------+ | 7B03CF04 | 0x01 | h09az | +----------+----------------+----------+ 1 row in set (0.00 sec) root@localhost:test 8.0.23> EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE status = '1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) Suggested fix: The root cause is when we don't force the optimizer to use primary index, the optimizer considers RANGE scan and tries to set up a min-max tree in test_quick_select() -> get_mm_tree(). In this process, the optimizer regards '1' as a string and saves its ASCII code into the field of type BIT(1). Of course, the value overflows and the optimizer directly returns the following trace: "setup_range_conditions": [ { "impossible_condition": { "cause": "value_out_of_range" } } ], "memory_capacity_exceeded": false, "impossible_range": true With the second query using primary scan, the comparator for the where condition is set up to regard both sides as real types in Arg_comparator::set_cmp_func(). So the executor regards '1' as integer 1, which equals to the decimal representation of bit b'1'.