Key not used if IN() list contains mix of strings and integers

Bug #622458 reported by Hartmut Holzgraefe on 2010-08-22
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Won't Fix
Medium
Andrew Hutchings
7.0
Won't Fix
Medium
Andrew Hutchings
MySQL Server
Unknown
Unknown

Bug Description

See http://bugs.mysql.com/bug.php?id=55979

This optimizer bug also exists in drizzle

How to reproduce

  DROP TABLE IF EXISTS t1;

  CREATE TABLE `t1` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `totalhits` INT DEFAULT NULL,
    PRIMARY KEY (`id`)
  );

  INSERT INTO t1 SELECT NULL, 0;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;

  EXPLAIN SELECT * FROM t1 WHERE id IN (1,2);
  EXPLAIN SELECT * FROM t1 WHERE id IN ('1','2');
  EXPLAIN SELECT * FROM t1 WHERE id IN (1,'2');

last explain shows a full table scan while the other two are using in index as expected

added the test case to my linked branch lp:~hartmut-php/drizzle/drizzle-bug622458

test can be run with

  cd tests; ./test-run --suite=broken wrong-plan-with-IN-bug-lp622458

current result is

  broken.wrong-plan-with-IN-bug-lp622458 [ fail ]
  --- /home/hartmut/projects/drizzle/bzr/drizzle-bug622458/tests/suite/broken/r/wrong-plan-with-IN-bug-lp622458.result 2010-08-23 20:10:59.000000000 +0300
  +++ /home/hartmut/projects/drizzle/bzr/drizzle-bug622458/tests/suite/broken/r/wrong-plan-with-IN-bug-lp622458.reject 2010-08-23 20:13:11.000000000 +0300
  @@ -17,4 +17,4 @@
   1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
   EXPLAIN SELECT * FROM t1 WHERE id IN (1,'2');
   id select_type table type possible_keys key key_len ref rows Extra
  -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where
  +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16 Using where

  drizzletest: Result length mismatch

as the query variant with mixed integer and string expressions in the IN() argument list leads to a suboptimal query plan (full table scan instead of primary key range scan)

Changed in drizzle:
assignee: nobody → Hartmut Holzgraefe (hartmut-php)
importance: Undecided → Medium
status: New → Confirmed
Changed in drizzle:
assignee: Hartmut Holzgraefe (hartmut-php) → Andrew Hutchings (linuxjedi)
Andrew Hutchings (linuxjedi) wrote :

This is intentional. The optimizer counts the number of types inside the IN() parameter list, if this is != 1 then we throw out any idea of being able to use the key. See "case Item_func::IN_FUNC" in "optimizer::SEL_TREE" and "Item_func_in::fix_length_and_dec()" for more info.

If there is any bug here it is that we are accepting string parameters at all for an INT column, but that is something for another time.

Changed in drizzle:
status: Triaged → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.