Bug #108192 Different results return when executing the same query with different plans
Submitted: 18 Aug 2022 13:16 Modified: 18 Aug 2022 13:50
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.23, 8.0.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[18 Aug 2022 13:16] Hope Lee
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'.
[18 Aug 2022 13:50] MySQL Verification Team
Hello Hope Lee,

Thank you for the report and feedback.

regards,
Umesh