Bug #45680 wrong results when using index for lookup with implicitly casted values
Submitted: 23 Jun 2009 14:15
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.54, 5.0.82, 5.1.35, 6.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious)

[23 Jun 2009 14:15] Shane Bester
Description:
The following two queries produce different results, when they should be identical.  All engines are affected by this.

mysql> select id from tmp1 ignore index(id) where id='\n1';
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select id from tmp1 force index(id) where  id='\n1';
Empty set (0.00 sec)

How to repeat:
drop table if exists tmp1;
create table tmp1(id int,key id (id))engine=myisam;
insert into tmp1 values (1),(2);
select id from tmp1 ignore index(id) where id='\n1';
select id from tmp1 force  index(id) where id='\n1';

Suggested fix:
users must create queries that are well formed. in other words, don't compare strings to numbers.  if you must compare strings to numbers, make sure the strings contain well formed numbers only.
[23 Jun 2009 14:15] Shane Bester
would be nice to fix this.  some qa programs are specifically testing the fact that using an index as apposed to table scan makes no difference for the number of rows returned in the resultset.  there could well be legitimate bugs here that are masked by this bug.
[23 Jun 2009 14:27] Shane Bester
4.1.24 and 5.0.27 returned expected identical results...
[23 Jun 2009 17:38] Shane Bester
Another testcase:

drop table if exists `tmp1`;
create table `tmp1` (`col0` time,`col1` double,`id` int not null primary key,key (`col1`), key (`col0`)) engine=myisam;

insert into `tmp1` values ('00:00:00',-1,1);
insert into `tmp1` values ('43:30:23',2,2);

select sql_no_cache `id` from `tmp1` 
force  index(`col0`,`col1`) where `col0` <= 666666 and `col1` >= 1 ;

select sql_no_cache `id` from `tmp1` 
ignore index(`col0`,`col1`) where `col0` <= 666666 and `col1` >= 1 ;

Then, we also get varying results for myisam, innodb, falcon, memory!
So, there are two inconsistencies:

1) different results with and without indexes.
2) different results with different storage engines.
[14 Feb 2013 9:53] Hartmut Holzgraefe
still exists in 5.6.10