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

[Hartmut Holzgraefe]

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

This optimizer bug also exists in drizzle

[Hartmut Holzgraefe (hartmut-php)]

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

[Hartmut Holzgraefe (hartmut-php)]

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)

[Andrew Hutchings (linuxjedi)]

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.