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

[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] MySQL Verification Team
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] MySQL Verification Team
4.1.24 and 5.0.27 returned expected identical results...
[23 Jun 2009 17:38] MySQL Verification Team
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
[1 Apr 2020 13:16] MySQL Verification Team
things seem a bit better for these testcases on 8.0.19.

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> drop table if exists tmp1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tmp1(id int,key id (id))engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tmp1 values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

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';
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> drop table if exists `tmp1`;
Query OK, 0 rows affected (0.01 sec)

mysql> create table `tmp1` (`col0` time,`col1` double,`id` int not null primary key,key (`col1`), key (`col0`)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> insert into `tmp1` values ('00:00:00',-1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `tmp1` values ('43:30:23',2,2);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select sql_no_cache `id` from `tmp1` 
    -> force  index(`col0`,`col1`) where `col0` <= 666666 and `col1` >= 1 ;
+----+
| id |
+----+
|  2 |
+----+
1 row in set, 3 warnings (0.01 sec)

mysql> 
mysql> select sql_no_cache `id` from `tmp1` 
    -> ignore index(`col0`,`col1`) where `col0` <= 666666 and `col1` >= 1 ;
+----+
| id |
+----+
|  2 |
+----+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1681 | 'SQL_NO_CACHE' is deprecated and will be removed in a future release. |
| Warning | 1292 | Incorrect time value: '666666' for column 'col0' at row 1             |
+---------+------+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 8.0.19-asan |
+-------------+
1 row in set (0.00 sec)