MySQL Bugs: #93005: 8.2.1.4 "The Index Merge Sort-Union Access Algorithm" is not correct

Bug #93005	8.2.1.4 "The Index Merge Sort-Union Access Algorithm" is not correct
Submitted:	30 Oct 2018 7:32	Modified:	31 Oct 2018 2:11
Reporter:	ashe sun (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The Index Merge Sort-Union Access Algorithm
This access algorithm is applicable when the WHERE clause is converted to several range conditions
combined by OR, but the Index Merge union algorithm is not applicable.
Examples:
SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm
must first fetch row IDs for all rows and sort them before returning any

How to repeat:
-

Hi,

I don't see a bug here?! Our optimizer is not perfect (please try 8.0, it's better than 5.7) but we are improving it all the time. 

Thanks
Bogdan

1、this  query  in documentation  8.2.14 will never use  index_merge_sort_union, 
SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;

show it to  you:
1.1、table definition:
mysql> show create table test_index_merge\G
*************************** 1. row ***************************
       Table: test_index_merge
Create Table: CREATE TABLE `test_index_merge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_name` varchar(10) DEFAULT NULL,
  `l_name` varchar(10) DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '10',
  `a_name` varchar(10) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `f_name` (`f_name`),
  KEY `l_name` (`l_name`),
  KEY `a_name` (`a_name`,`b_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2031447 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

1.2、explain res:
mysql> explain select * from test_index_merge where a_name = 'c' or b_name = 'b';
+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_index_merge | NULL       | ALL  | a_name        | NULL | NULL    | NULL | 1832564 |    19.00 | Using where |
+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2、I think this is the correct example 

2.1、the same table definition as shown above；

2.2、
mysql> explain select *  from test_index_merge where a_name='a' or id < 100;
+----+-------------+------------------+------------+-------------+----------------+----------------+---------+------+--------+----------+-----------------------------------------------+
| id | select_type | table            | partitions | type        | possible_keys  | key            | key_len | ref  | rows   | filtered | Extra                                         |
+----+-------------+------------------+------------+-------------+----------------+----------------+---------+------+--------+----------+-----------------------------------------------+
|  1 | SIMPLE      | test_index_merge | NULL       | index_merge | PRIMARY,a_name | a_name,PRIMARY | 43,4    | NULL | 135571 |   100.00 | Using sort_union(a_name,PRIMARY); Using where |
+----+-------------+------------------+------------+-------------+----------------+----------------+---------+------+--------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

3、When the where clauses contain first part of muti-column index，this rowid or primary key retrieved from that muti-column index is not ordered like(1,2,3,4),it may be (1,4,3,2), and so it must do a sort operation to merge union with  id < 100，and this is named sort_union,firstly sort，and secondly merge union

Hi,

for [1] the query that you are using is not the same as query you are talking about

> ... SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

and then you are running explain on 

> mysql> explain select * from test_index_merge where a_name = 'c' or b_name = 'b';

There is no key on b_name in your table so there's nothing to merge? The only possible key is a_name and using it will not speed up anything due to "or b_name" that's not in any index, this is really a full table scan operation and has nothing to do with index_merge.

With regards to [2], 

> explain select *  from test_index_merge where a_name='a' or id < 100;

I don't see a problem here? Explain is showing it will do index merge with primary key and a_name. You believe that instead of "Using sort_union(a_name,PRIMARY);" we should be "Using union(a_name,PRIMARY);" ?

Thanks
Bogdan