Bug #101459 With more than one value in IN() clause, query skip index use.

Description:
Select Queries like in below example with more than one value in IN() clause, skip index use.

Issue Reproduciable with 5.6.50 and 5.7.32, Same issue not reproduciable with 8.0.21

mysql > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: inx_key1,inx_key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

How to repeat:
Issue Reproduciable with 5.6.50 and 5.7.32, Same issue not reproduciable with 8.0.21

Test case:
CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(10) DEFAULT 'new' ,
`priority` int(11) DEFAULT '300',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `inx_key1` (`status`,`priority`),
KEY `inx_key2` (`status`,`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL 5.6.50

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: inx_key1,inx_key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: inx_key1,inx_key2
          key: inx_key2
      key_len: 33
          ref: const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

MySQL 5.7.32

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: inx_key1,inx_key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: inx_key1,inx_key2
          key: inx_key2
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

1 row in set (0.00 sec)

********************
MySQL-8.0.21  works fine:

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: inx_key1,inx_key2
          key: inx_key2
      key_len: 33
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: inx_key1,inx_key2
          key: inx_key2
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > exit
Bye

Suggested fix:
It should use index regardless of the number of values specified in IN() clause.

Hello Lalit,

Thank you for the report and feedback!

Thanks,
Umesh

same issue with AND clause as well,

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status='abc' and status='xyz' and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status='abc'  and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: inx_key1,inx_key2
          key: inx_key2
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) >

Same for MySQL 5.7.37:

mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.37                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.37-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

mysql> explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: inx_key1,inx_key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 80.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)