Bug #13371 Wrong result set using WHERE conditions on an indexed column

• Submitted: 21 Sep 2005 9:20
• Modified: 21 Sep 2005 10:20
• Reporter: Sven Reifegerste
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: Ver 14.7 Distrib 4.1.11 (i-386)
• OS: Linux (Linux (Debian, Suse))
• CPU Architecture: Any

[21 Sep 2005 9:20] Sven Reifegerste

Description:
The following query on the following simple table 

+----+
| id |
+----+
|  1 |
|  1 |
+----+

gives an empty result set if 'id' is an indexed int-column, although it is obviously that we should get two results:

SELECT * FROM keytest
WHERE (id=4
OR (id>=1 AND id<=3)
OR (id>=2 AND id<=5));

or respectively:

SELECT * FROM keytest
WHERE (id IN (4)
OR (id BETWEEN 1 AND 3)
OR (id BETWEEN 2 AND 5));

If _no_ index is used, everything works fine.

How to repeat:
Errorneous (index used):
-----------------------
CREATE TABLE keytest (id int(10) NOT NULL default '1', KEY id (id));
INSERT INTO keytest VALUES (1);
INSERT INTO keytest VALUES (1);
SELECT * FROM keytest WHERE (id=4 OR (id>=1 AND id<=3) OR (id>=2 AND id<=5));

-> empty result set

Correct (no index used):
-----------------------
CREATE TABLE keytest (id int(10) NOT NULL default '1');
INSERT INTO keytest VALUES (1);
INSERT INTO keytest VALUES (1);
SELECT * FROM keytest WHERE (id=4 OR (id>=1 AND id<=3) OR (id>=2 AND id<=5));

-> 2 results

Suggested fix:
Because without index everything works fine, the problem must be with the index and a combination of sorting (overlapping) equality and range conditions. The phenoma only occurs if the table has two or more entries. All three OR-conditions are necessary to get the error.

Suggestion: Check out the use of an index in combination of overlapping conditions, especially if the equality condition meets exactly one of two overlapping range conditions.

[21 Sep 2005 10:20] Valeriy Kravchuk

I've tried to repeat the described behaviour on newer 4.1.14 version:

mysql> use test;
Database changed
mysql> CREATE TABLE keytest (id int(10) NOT NULL default '1', KEY id (id));
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO keytest VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO keytest VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM keytest WHERE (id=4 OR (id>=1 AND id<=3) OR (id>=2 AND id<=5));
+----+
| id |
+----+
|  1 |
|  1 |
+----+
2 rows in set (0.01 sec)

mysql> explain SELECT * FROM keytest WHERE (id=4 OR (id>=1 AND id<=3) OR (id>=2 AND id<=5));
+----+-------------+---------+-------+---------------+------+---------+------+--
----+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | r
ows | Extra                    |
+----+-------------+---------+-------+---------------+------+---------+------+--
----+--------------------------+
|  1 | SIMPLE      | keytest | range | id            | id   |       4 | NULL |
  1 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--
----+--------------------------+
1 row in set (0.00 sec)

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

So, everythink works as expected. Please, try to use newer version of MySQL.