MySQL Bugs: #51946: 'Range' optimized access returns incorrect result (duplicates of rows)

Bug #51946	'Range' optimized access returns incorrect result (duplicates of rows)
Submitted:	11 Mar 2010 9:23	Modified:	11 Mar 2010 11:20
Reporter:	Ole John Aske	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
'Range' index access may duplicate the same rows in the result set. The example query has the following explain:

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

Looking at 'mysqld.trace' produced by this query, the following ranges are reported by the optimizer:

1: X < 2
2: X < 4
3: X > 4

As 1) and 2) overlaps, it is my guess that both of them will cause the
rows (0,0), (1,1) to be retrieved.

The same bug is reproducible on other SEs *without* 'FORCE INDEX()' if they
return a higher cost in their implementation of handler::scan_time(). One such
example is ndbcluster:

drop table test;
create table test (pk int primary key, int_key int unique key) engine = ndbcluster;
insert into test(pk,int_key) value (0,0), (1,1);

SELECT * FROM test
 WHERE (pk IN (2,3) OR pk != 4);

+----+---------+
| pk | int_key |
+----+---------+
|  1 |       1 |
|  1 |       1 |
|  0 |       0 |
|  0 |       0 |
+----+---------+
4 rows in set (0.01 sec)

How to repeat:
create table test (pk int primary key, int_key int unique key) engine = myisam;
insert into test(pk,int_key) value (0,0), (1,1);

SELECT * FROM test FORCE INDEX(PRIMARY)
 WHERE (pk IN (2,3) OR pk != 4);

+----+---------+
| pk | int_key |
+----+---------+
|  0 |       0 |
|  1 |       1 |
|  0 |       0 |
|  1 |       1 |
+----+---------+
4 rows in set (0.01 sec)

Please, check with a newer version, 5.1.44, and inform about the results. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test (pk int primary key, int_key int unique key) engine = m
yisam;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into test(pk,int_key) value (0,0), (1,1);
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test FORCE INDEX(PRIMARY)
    ->  WHERE (pk IN (2,3) OR pk != 4);
+----+---------+
| pk | int_key |
+----+---------+
|  0 |       0 |
|  1 |       1 |
+----+---------+
2 rows in set (0.22 sec)

This bug seems to be fixed in 5.1.42 - It might be time for the telco team to merge from the main branch.

Looking at the changelog for 5.1.42 this is most likely e duplicate of bug#48665 - Not easy to see the similarities from the synopsis though....

Duplicate of Bug #48665 it seems.