MySQL Bugs: #72040: mysql only returning the first row

Bug #72040	mysql only returning the first row
Submitted:	14 Mar 2014 21:28	Modified:	13 May 2014 12:09
Reporter:	mathieu mat	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.35,5.5.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, primary key

Description:
When using a multiple-column primary key and an always true condition (y<=3 OR y>3), MySQL only return the first row match.

If we had an IGNORE KEY(primary) to the request, MySQL return all the attended rows

This bug occurs with Innodb engine only.

How to repeat:
CREATE TABLE `test` (x tinyint unsigned NOT NULL, y tinyint unsigned NOT
NULL, z tinyint unsigned, PRIMARY KEY (`x`,`y`)) ENGINE=InnoDB DEFAULT
CHARSET=utf8;

INSERT INTO test VALUES
(1,1,0),(1,2,0),(1,3,0),(1,4,0),(2,1,0),(2,2,0),(2,3,0);

SELECT * FROM test WHERE x=1 AND (y<=3 OR y>3);

SELECT * FROM test IGNORE KEY(primary) WHERE x=1 AND (y<=3 OR y>3);

Verified on 5.5.36 (Windows):

mysql> SELECT * FROM test WHERE x=1 AND (y<=3 OR y>3);
+---+---+------+
| x | y | z    |
+---+---+------+
| 1 | 1 |    0 |
+---+---+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM test IGNORE KEY(primary) WHERE x=1 AND (y<=3 OR y>3);
+---+---+------+
| x | y | z    |
+---+---+------+
| 1 | 1 |    0 |
| 1 | 2 |    0 |
| 1 | 3 |    0 |
| 1 | 4 |    0 |
+---+---+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE test ENGINE = MyISAM;
Query OK, 7 rows affected (0.34 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE x=1 AND (y<=3 OR y>3);
+---+---+------+
| x | y | z    |
+---+---+------+
| 1 | 1 |    0 |
| 1 | 2 |    0 |
| 1 | 3 |    0 |
| 1 | 4 |    0 |
+---+---+------+
4 rows in set (0.00 sec)

Not repeatable in 5.5.34:

D:\mysql-5.5.34-winx64>bin\mysql -uroot -P3305 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE `test` (x tinyint unsigned NOT NULL, y tinyint unsigned NOT
    -> NULL, z tinyint unsigned, PRIMARY KEY (`x`,`y`)) ENGINE=InnoDB DEFAULT
    -> CHARSET=utf8;
Query OK, 0 rows affected (0.55 sec)

mysql>
mysql> INSERT INTO test VALUES
    -> (1,1,0),(1,2,0),(1,3,0),(1,4,0),(2,1,0),(2,2,0),(2,3,0);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test WHERE x=1 AND (y<=3 OR y>3);
+---+---+------+
| x | y | z    |
+---+---+------+
| 1 | 1 |    0 |
| 1 | 2 |    0 |
| 1 | 3 |    0 |
| 1 | 4 |    0 |
+---+---+------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM test IGNORE KEY(primary) WHERE x=1 AND (y<=3 OR y>3);
+---+---+------+
| x | y | z    |
+---+---+------+
| 1 | 1 |    0 |
| 1 | 2 |    0 |
| 1 | 3 |    0 |
| 1 | 4 |    0 |
+---+---+------+
4 rows in set (0.00 sec)

Posted by developer:
 
Unable to reproduce in 5.5.34, regression introduced in 5.5.35 and present in 5.5.36.

Reproduced on 5.5.36/Windows x64

Posted by developer:
 
Reassigned to the optimiser team based on Bin's analysis.

This appears to be some bug in the range optimizer.
It affects 5.5 only. 5.6 and trunk are not affected.

Posted by developer:
 
This bug is already fixed in 5.6 and upwards. It is too risky to fix it in 5.5.

this regression was apparently introduced by the fix for
https://bugs.mysql.com/bug.php?id=68656