MySQL Bugs: #62940: SELECT results vary with version and with/without index range scan

Bug #62940	SELECT results vary with version and with/without index range scan
Submitted:	26 Oct 2011 6:51	Modified:	24 Dec 2012 9:32
Reporter:	Stein Haugan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.39, 5.5.10, 5.5.20	OS:	Any (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
SELECT statements that use range key scans produce different results in MySQL v. 5.0 [correct result] vs 5.1 and 5.5. When the table is altered to prevent range scans (no usable index to perform such scans), the difference disappears.

This is (probably) due to round-off *prior* to sending the key values down to the storage engine's range scan method.

How to repeat:
/* Set up table used for testing: */
use test;
drop table if exists numbers;
create table numbers (id int unsigned not null auto_increment primary key);
insert into numbers values (null);  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
insert into numbers select null from numbers;  
drop table if exists tmy;
create table tmy  (id int unsigned not null auto_increment, val decimal(5,3) not null, primary key (id,val), unique key (val,id), unique key (id));  
insert into tmy select null,id*0.0009 from numbers;

/* Scenario set-up complete. */

select count(val) from tmy where val > 0.1155; /* Result: 127 */
alter table tmy drop key val;                              /* Prevents range scan from occurring */
select count(val) from tmy where val > 0.1155; /* Result: 128 */

Suggested fix:
Round off *down* for lower limit (min_key) of range scan, round off *up* for upper limit (max_key) of range scan, then filter results as is done when e.g. a simple scan is performed.

I consider this a serious problem: a database that gives wrong answers is a terrible thing! It's even worse if it gives different answers depending on the table indices and/or MySQL version number. This could easily lead to application crashes due to FOR loops that use different tables w/same data.

A temporary workaround is to leave the rounding down/up + filtering to the application, but the number of legacy issues are enormous!

Sorry: The PRIMARY and 'id' keys are not required to repeat the problem! Disregard.

What exact versions, 5.1.x and 5.5.y, are affected?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

It occurs in versions 5.5.10-log and 5.1.39. 
It does *not* occur in 5.0.91.

Please, check if this problem still happens with recent versions, 5.1.60 and/or 5.5.19.

Yes, 5.5.19. Haven't tested 5.1.60, but given the fundamental problem (round-off before sending values into storage engine) there's no reason why it should have been fixed in 5.5 but not in 5.1.

OK, I still see the problem even with current mysql-5.5 (5.5.20) on Mac OS X:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.20-debug |
+--------------+
1 row in set (0.93 sec)

mysql> drop table if exists numbers;
Query OK, 0 rows affected, 1 warning (0.36 sec)

mysql> create table numbers (id int unsigned not null auto_increment primary key);
Query OK, 0 rows affected (0.88 sec)

mysql> insert into numbers values (null);  
Query OK, 1 row affected (0.13 sec)

mysql> insert into numbers select null from numbers;  
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 64 rows affected (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into numbers select null from numbers;  
Query OK, 128 rows affected (0.05 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> drop table if exists tmy;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tmy  (id int unsigned not null auto_increment, val decimal(5,3) not null,
    -> primary key (id,val), unique key (val,id), unique key (id));  
Query OK, 0 rows affected (0.33 sec)

mysql> insert into tmy select null,id*0.0009 from numbers;
Query OK, 256 rows affected, 230 warnings (0.29 sec)
Records: 256  Duplicates: 0  Warnings: 230

mysql> select count(val) from tmy where val > 0.1155;
+------------+
| count(val) |
+------------+
|        184 |
+------------+
1 row in set (0.09 sec)

mysql> alter table tmy drop key val;    
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(val) from tmy where val > 0.1155;
+------------+
| count(val) |
+------------+
|        185 |
+------------+
1 row in set (0.00 sec)

Fixed as a duplicate of bug#63437.