Description:
I have a table with a varchar(10) column, and it is indexed.
When I perform a "select * from table where column_name in ('value1','value2')", I come across the below issue based on values being passed.
Conditions:
1. If length(value1) <=10 and length(value2) <= 10 characters, index is picked up.
2. If only one value is passed and its length is more than 10, index is picked up.
3. If length(value1) <= 10 and length(value2) > 10, index is not picked up.
We recently upgraded from 8.0.40 to 8.4.3 and encountered this problem in the new version.
How to repeat:
Condition 1:
mysql> explain select id from bugtest where bug in ('value1','value2');
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | bugtest | NULL | range | bug_ndx | bug_ndx | 43 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Condition 2:
mysql> explain select id from bugtest where bug in ('value1_greater_than_10');
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | bugtest | NULL | ref | bug_ndx | bug_ndx | 43 | const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Condition 3:
mysql> explain select id from bugtest where bug in ('value1_greater_than_10','value2');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | bugtest | NULL | ALL | bug_ndx | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
Suggested fix:
In this case, the optimizer should work the same way as 8.0.40.
Description: I have a table with a varchar(10) column, and it is indexed. When I perform a "select * from table where column_name in ('value1','value2')", I come across the below issue based on values being passed. Conditions: 1. If length(value1) <=10 and length(value2) <= 10 characters, index is picked up. 2. If only one value is passed and its length is more than 10, index is picked up. 3. If length(value1) <= 10 and length(value2) > 10, index is not picked up. We recently upgraded from 8.0.40 to 8.4.3 and encountered this problem in the new version. How to repeat: Condition 1: mysql> explain select id from bugtest where bug in ('value1','value2'); +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | bugtest | NULL | range | bug_ndx | bug_ndx | 43 | NULL | 2 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) Condition 2: mysql> explain select id from bugtest where bug in ('value1_greater_than_10'); +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | bugtest | NULL | ref | bug_ndx | bug_ndx | 43 | const | 1 | 100.00 | Using index condition | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) Condition 3: mysql> explain select id from bugtest where bug in ('value1_greater_than_10','value2'); +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | bugtest | NULL | ALL | bug_ndx | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ Suggested fix: In this case, the optimizer should work the same way as 8.0.40.