Key not used if IN() list contains mix of strings and integers
Bug #622458 reported by
Hartmut Holzgraefe
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://
This optimizer bug also exists in drizzle
Related branches
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) |
To post a comment you must log in.
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