bug?? partial index gives wrong results with IN subquery

[Paul Davies]

Hi all, is this a bug in the Optimizer? If so, does it have a number already?

A varchar(255) column col1 is indexed on the first 10 characters only: when I use the values in col1 to filter rows in another table, mySQL only seems to test the first 10 characters of each value, ignoring the rest completely.

Version() is 4.1.10a on Linux, using MyISAM. The value which my query "sees" in col1, but is not actually there, is 'Industrial Equipment'. The values in the table include 4 which start 'Industrial' (e.g.'Industrial Gears') but NOT 'Industrial Equipment'.

Query "select distinct category from table2 where category like 'indust%nt' and category in (select col1 from table1);"
returns:
+----------------------+
| category |
+----------------------+
| Industrial Equipment |
+----------------------+
1 row in set (0.26 sec)
(the "like" clause is just to limit the results for this test)
If I drop the index on col1, the query correctly returns an empty set. So the wrong results are because of the optimizer using the indexed values instead of the actual data values. But mySQL should(?) know it can't rely on the indexed values for a partial index...



Edited 1 time(s). Last edit at 02/26/2007 10:04AM by Paul Davies.