Index not used when mixing string and number literals in IN-condition

Description

PK is used for a simple lookup by number literal:

1 2 3 4 5 6 MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = 2300103499779; +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | items | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | | +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

PK is also used for a simple lookup by string literal:

1 2 3 4 5 6 MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = '2300103499779'; +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | items | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | | +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

PK is not used when using a mix of string and number literals:

1 2 3 4 5 6 MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id IN ('2300103499779', 2300103499779); +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | items | ALL | PRIMARY | NULL | NULL | NULL | 16060958 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

Environment

Ubuntu 14.04

Status

Assignee

Alexander Barkov

Reporter

Christian Rishøj

Labels

External issue ID

None

External issue ID

None

Components

Fix versions

Affects versions

10.0
10.1
5.5

Priority

Minor