Bug #116515 For mysql8.0.32 version,the skip scan result is different when condition is same

• Submitted: 31 Oct 2024 6:45
• Modified: 31 Oct 2024 11:18
• Reporter: wu sunny
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any
• Tags: mm tree, Optimizer, skip scan

[31 Oct 2024 6:45] wu sunny

Description:
select covering index columns use same condition but result is different.When use WHERE c3 >= 40 and 0<c1<3 and WHERE c3 >= 40 and 0<c1 and c1<3,c1 is prefix column,c3 is after column,but same condition make different mm tree.
c3 >= 40 and 0<c1<3           ===> c3 >= 40
c3 >= 40 and 0<c1 and c1<3    ===> c3 >= 40 ->next_key_part 0<c1<3
and then c3 >= 40 can use skip scan,but 0<c1<3 can't.

How to repeat:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT);
CREATE UNIQUE INDEX i1_t1 ON t1(c1,c2,c3);
INSERT INTO t1 VALUES (1,1,1,1), (1,1,2,2), (1,3,3,3), (1,4,4,4), (1,5,5,5),
                      (2,1,1,1), (2,2,2,2), (2,3,3,3), (2,4,4,4), (2,5,5,5);
INSERT INTO t1 SELECT c1, c2, c3+5, c4+10  FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+10, c4+20 FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+20, c4+40 FROM t1;
INSERT INTO t1 SELECT c1, c2, c3+40, c4+80 FROM t1;
ANALYZE TABLE t1;

> EXPLAIN SELECT c1, c2,c3 FROM t1 WHERE c3 >= 40 and 0<c1<3;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | i1_t1         | i1_t1 | 15      | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------+

> EXPLAIN SELECT c1, c2,c3 FROM t1 WHERE c3 >= 40 and 0<c1 and c1<3;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | i1_t1         | i1_t1 | 15      | NULL |  160 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+

Suggested fix:
in range_analysis.cc:
get_mm_tree() {
    default: {
      Item *const arg_left = cond_func->arguments()[0];

      assert(!ftree);
      if (!arg_left->is_outer_reference() &&
          arg_left->real_item()->type() == Item::FIELD_ITEM) {
      }
}
the problem is arg_left->real_item()->type() == Item::FIELD_ITEM,for Item 0<c1<3,
arg_left is Item_func_le and arg_right is 3,so it can't do get_full_func_mm_tree(),so Item 0<c1<3 can't make mm tree.

[31 Oct 2024 11:18] MySQL Verification Team

Hi Mr. sunny,

Thank you for your bug report.

However, this is not a bug.

Simply, according to SQL standard you can not define range with a condition like this one:

SELECT c1, c2,c3 FROM t1 WHERE c3 >= 40 and 0<c1<3;

You should use instead:

SELECT c1, c2,c3 FROM t1 WHERE c3 >= 40 and c1 BETWEEN 0 AND 3;

Not a bug.
;