Description:
When a SELECT query uses OR conditions across multiple indexed columns, and one OR branch contains a string value that exceeds the column's defined length, the optimizer skips Index Merge Union entirely and falls back to a full table scan.
Logically, `A OR B OR IMPOSSIBLE` should be equivalent to `A OR B`, but the current behavior abandons Index Merge optimization for all conditions.
After reviewing the source code, I believe the root cause is in `tree.cc`, function `tree_or()`:
```cpp
if (tree2->type == SEL_TREE::IMPOSSIBLE || tree1->type == SEL_TREE::ALWAYS)
return tree1;
```
When one OR branch is marked as IMPOSSIBLE (due to value truncation), the function returns early without proceeding to the Index Merge construction logic.
How to repeat:
MySQL 8.4.8 with default settings, installed from Oracle's MySQL YUM repository.
```sql
mysql> CREATE TABLE `t` (
-> `id` bigint NOT NULL,
-> `c1` varchar(2) NULL,
-> `c2` varchar(2) NULL,
-> `c3` varchar(2) NULL,
-> `c4` varchar(2) NULL,
-> PRIMARY KEY (`id`) USING BTREE,
-> KEY `idx_c1` (`c1`) USING BTREE,
-> KEY `idx_c2` (`c2`) USING BTREE,
-> KEY `idx_c3` (`c3`) USING BTREE
-> );
mysql> INSERT INTO `t` (id, c1,c2,c3,c4) VALUES (1,'a1','b1','c1','d1'), (2,'a1','b1','c1','d1'), (3,'a1','b1','c1','d1'), (4,'a1','b1','c1','d1'), (5,'a1','b1','c1','d1');
mysql> EXPLAIN SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'c';
+----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+
| 1 | SIMPLE | t | NULL | index_merge | idx_c1,idx_c2,idx_c3 | idx_c1,idx_c2,idx_c3 | 11,11,11 | NULL | 3 | 100.00 | Using union(idx_c1,idx_c2,idx_c3); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+
mysql> EXPLAIN ANALYZE SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'c';
| -> Filter: ((t.c1 = 'a') or (t.c2 = 'b') or (t.c3 = 'c')) (cost=2.2 rows=3) (actual time=0.0393..0.0393 rows=0 loops=1)
-> Deduplicate rows sorted by row ID (cost=2.2 rows=3) (actual time=0.0385..0.0385 rows=0 loops=1)
-> Index range scan on t using idx_c1 over (c1 = 'a') (cost=0.36 rows=1) (actual time=0.0292..0.0292 rows=0 loops=1)
-> Index range scan on t using idx_c2 over (c2 = 'b') (cost=0.36 rows=1) (actual time=0.00395..0.00395 rows=0 loops=1)
-> Index range scan on t using idx_c3 over (c3 = 'c') (cost=0.36 rows=1) (actual time=0.00329..0.00329 rows=0 loops=1)
|
mysql> EXPLAIN SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'abc';
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | idx_c1,idx_c2,idx_c3 | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN ANALYZE SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'abc';
| -> Filter: ((t.c1 = 'a') or (t.c2 = 'b') or (t.c3 = 'abc')) (cost=0.75 rows=5) (actual time=0.0306..0.0306 rows=0 loops=1)
-> Table scan on t (cost=0.75 rows=5) (actual time=0.023..0.0266 rows=5 loops=1)
|
```
Suggested fix:
In an ideal scenario, when one OR branch is IMPOSSIBLE (due to value truncation), the function could continue processing to allow Index Merge construction for the remaining valid predicates, rather than returning early.
Description: When a SELECT query uses OR conditions across multiple indexed columns, and one OR branch contains a string value that exceeds the column's defined length, the optimizer skips Index Merge Union entirely and falls back to a full table scan. Logically, `A OR B OR IMPOSSIBLE` should be equivalent to `A OR B`, but the current behavior abandons Index Merge optimization for all conditions. After reviewing the source code, I believe the root cause is in `tree.cc`, function `tree_or()`: ```cpp if (tree2->type == SEL_TREE::IMPOSSIBLE || tree1->type == SEL_TREE::ALWAYS) return tree1; ``` When one OR branch is marked as IMPOSSIBLE (due to value truncation), the function returns early without proceeding to the Index Merge construction logic. How to repeat: MySQL 8.4.8 with default settings, installed from Oracle's MySQL YUM repository. ```sql mysql> CREATE TABLE `t` ( -> `id` bigint NOT NULL, -> `c1` varchar(2) NULL, -> `c2` varchar(2) NULL, -> `c3` varchar(2) NULL, -> `c4` varchar(2) NULL, -> PRIMARY KEY (`id`) USING BTREE, -> KEY `idx_c1` (`c1`) USING BTREE, -> KEY `idx_c2` (`c2`) USING BTREE, -> KEY `idx_c3` (`c3`) USING BTREE -> ); mysql> INSERT INTO `t` (id, c1,c2,c3,c4) VALUES (1,'a1','b1','c1','d1'), (2,'a1','b1','c1','d1'), (3,'a1','b1','c1','d1'), (4,'a1','b1','c1','d1'), (5,'a1','b1','c1','d1'); mysql> EXPLAIN SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'c'; +----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+ | 1 | SIMPLE | t | NULL | index_merge | idx_c1,idx_c2,idx_c3 | idx_c1,idx_c2,idx_c3 | 11,11,11 | NULL | 3 | 100.00 | Using union(idx_c1,idx_c2,idx_c3); Using where | +----+-------------+-------+------------+-------------+----------------------+----------------------+----------+------+------+----------+------------------------------------------------+ mysql> EXPLAIN ANALYZE SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'c'; | -> Filter: ((t.c1 = 'a') or (t.c2 = 'b') or (t.c3 = 'c')) (cost=2.2 rows=3) (actual time=0.0393..0.0393 rows=0 loops=1) -> Deduplicate rows sorted by row ID (cost=2.2 rows=3) (actual time=0.0385..0.0385 rows=0 loops=1) -> Index range scan on t using idx_c1 over (c1 = 'a') (cost=0.36 rows=1) (actual time=0.0292..0.0292 rows=0 loops=1) -> Index range scan on t using idx_c2 over (c2 = 'b') (cost=0.36 rows=1) (actual time=0.00395..0.00395 rows=0 loops=1) -> Index range scan on t using idx_c3 over (c3 = 'c') (cost=0.36 rows=1) (actual time=0.00329..0.00329 rows=0 loops=1) | mysql> EXPLAIN SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'abc'; +----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | ALL | idx_c1,idx_c2,idx_c3 | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+ mysql> EXPLAIN ANALYZE SELECT * FROM `t` WHERE c1 = 'a' OR c2 = 'b' OR c3 = 'abc'; | -> Filter: ((t.c1 = 'a') or (t.c2 = 'b') or (t.c3 = 'abc')) (cost=0.75 rows=5) (actual time=0.0306..0.0306 rows=0 loops=1) -> Table scan on t (cost=0.75 rows=5) (actual time=0.023..0.0266 rows=5 loops=1) | ``` Suggested fix: In an ideal scenario, when one OR branch is IMPOSSIBLE (due to value truncation), the function could continue processing to allow Index Merge construction for the remaining valid predicates, rather than returning early.