MySQL Bugs: #45680: wrong results when using index for lookup with implicitly casted values

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

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.

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.

4.1.24 and 5.0.27 returned expected identical results...

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.

still exists in 5.6.10

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)