[MDEV-9029] Index not used when mixing string and number literals in IN-condition Created: 2015-10-28  Updated: 2017-04-20

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Christian Rishøj Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 3
Labels: upstream-fixed
Environment:

Ubuntu 14.04


 Description   

PK is used for a simple lookup by number literal:

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:

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:

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



 Comments   
Comment by Elena Stepanova [ 2015-11-26 ] 

drop table if exists t1;
 
create table t1 (pk int auto_increment primary key, f varchar(8));
 
insert into t1 values (null,'foo'),(null,'bar'),(null,'qux'),(null,'baz');
 
insert into t1 select null, t1a.f from t1 t1a, t1 t1b, t1 t1c;
 
 
 
analyze table t1;
 
 
 
explain select * from t1 where pk = 40;
 
explain select * from t1 where pk = '40';
 
explain select * from t1 where pk in (40,40);
 
explain select * from t1 where pk in ('40','40');
 
explain select * from t1 where pk in (40,'40');
 
explain select * from t1 where pk in ('40',40);
 
 
 
drop table t1;
 

MariaDB [test]> explain select * from t1 where pk = 40;
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> explain select * from t1 where pk = '40';
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> explain select * from t1 where pk in (40,40);
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
|    1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using index condition |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> explain select * from t1 where pk in ('40','40');
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
|    1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using index condition |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> explain select * from t1 where pk in (40,'40');
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL |   68 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> explain select * from t1 where pk in ('40',40);
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL |   68 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

It appears to be fixed in 5.7:

MySQL [test]> explain select * from t1 where pk in (40,'40');
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MySQL [test]> explain select * from t1 where pk in ('40',40);
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MySQL [test]> select @@version;
 
+-------------+
 
| @@version   |
 
+-------------+
 
| 5.7.9-debug |
 
+-------------+
 
1 row in set (0.00 sec)

Comment by Sergei Petrunia [ 2015-11-26 ]

bar is a bigger datatype expert than me. bar, could you take a look at this?

Comment by Alexander Barkov [ 2017-04-20 ]

IN fails to use the index because of this code in Item_func_in::get_func_mm_tree:

  /*
 
    Array for IN() is constructed when all values have the same result
 
    type. Tree won't be built for values with different result types,
 
    so we check it here to avoid unnecessary work.
 
  */
 
  if (!arg_types_compatible)
 
    DBUG_RETURN(0);

The field is compared:

  • to '40' as DOUBLE
  • to 40 as INTEGER

For every constant in the list, Item_func_in::get_func_mm_tree could be fixed to try the same thing what Item_bool_func::get_mm_leaf does:

  • store the values to the field using value->save_in_field_no_warnings(field, 1)
  • check the error and if no errors happened, then allow optimization.

Note, the code behind IN was significantly changed in 10.3 to address multiple problems, by this patch:

MDEV-11514, MDEV-11497, MDEV-11554, MDEV-11555 - IN and CASE type aggregation problems

So this bug should probably be fixed 10.3.

Generated at Thu Feb 08 07:31:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.