MySQL Bugs: #111991: index merge sometimes return incorrect result

Bug #111991	index merge sometimes return incorrect result
Submitted:	8 Aug 2023 3:25	Modified:	10 Aug 2023 5:52
Reporter:	Zhejun Cai	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.32,8.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Index merge sometimes return incorrect result.

At the first time, the following sql return same result.
select * from t1 where f1=1 or f2=2;
select  /*+INDEX_MERGE(t1)*/ * from t1 where f1=1 or f2=2;

But at the second time, index merge return empty result.

Server version: 8.1.0-debug Source distribution

1st:
mysql> source case_index_merge.sql
Query OK, 1 row affected (0.03 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.14 sec)

Query OK, 1 row affected (0.04 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | INDEX_f1,INDEX_f2 | INDEX_f1,INDEX_f2 | 5,5     | NULL |    1 |   100.00 | Using union(INDEX_f1,INDEX_f2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

2nd:
mysql> source case_index_merge.sql
Query OK, 1 row affected, 1 warning (0.00 sec)

Database changed
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 1 row affected (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    1 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

Empty set (0.01 sec)

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 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)

mysql> 

How to repeat:
Execute the following sql repeatedly, case_index_merge.sql:

create database if not exists test;

use test;

create table if not exists t1( id int auto_increment, f1 int default null, f2 int default null, primary key(id), key `INDEX_f1`(`f1`), key `INDEX_f2`(`f2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 

insert into t1(f1, f2) values(1,2);

select * from t1 where f1=1 or f2=2;

select /*+INDEX_MERGE(t1)*/ * from t1 where f1=1 or f2=2;

explain select /*+INDEX_MERGE(t1)*/ * from t1 where f1=1 or f2=2;

Hello Zhejun Cai,

Thank you for the report and test case.
I tried to reproduce as suggested but not seeing any issues. Is there anything I'm missing here? It would be good if you share the configuration file if you are not running the instance with default settings. Thank you.

##

rm -rf 111991/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/111991 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/111991 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/111991/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source case_index_merge.sql;
Query OK, 1 row affected (0.01 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 1 row affected (0.01 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | INDEX_f1,INDEX_f2 | INDEX_f1,INDEX_f2 | 5,5     | NULL |    1 |   100.00 | Using union(INDEX_f1,INDEX_f2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> source case_index_merge.sql;
Query OK, 1 row affected, 1 warning (0.01 sec)

Database changed
Query OK, 0 rows affected, 2 warnings (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    1 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    1 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | INDEX_f1,INDEX_f2 | INDEX_f1,INDEX_f2 | 5,5     | NULL |    2 |   100.00 | Using union(INDEX_f1,INDEX_f2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

regards,
Umesh

Hello Umesh,
Thank you for reminding me, I checked the configure file and found there are two special variables which i used to test some edge cases, 
table_definition_cache=1
table_open_cache=1
After i set them to default value, it returns ok. 
Please verify it again.

Thank you for the details, will get back to you if anything further needed on this.

Sincerely,
Umesh

-- 8.0.32

bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/111991 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/111991/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  --table_definition_cache=1 --table_open_cache=1 2>&1 &

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> source case_index_merge.sql;
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 1 row affected (0.01 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
+----+------+------+
1 row in set (0.00 sec)

+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | INDEX_f1,INDEX_f2 | INDEX_f1,INDEX_f2 | 5,5     | NULL |    1 |   100.00 | Using union(INDEX_f1,INDEX_f2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> source case_index_merge.sql;
Query OK, 1 row affected, 1 warning (0.00 sec)

Database changed
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 1 row affected (0.00 sec)

+----+------+------+
| id | f1   | f2   |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    1 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

Empty set (0.00 sec)

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 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)