MySQL Bugs: #39234: Mysql return empty set on select with range on primary key with order by + limit

Bug #39234	Mysql return empty set on select with range on primary key with order by + limit
Submitted:	4 Sep 2008 9:52	Modified:	10 Sep 2008 20:34
Reporter:	Grégory Clause	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	cluster 6.2.15	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	select ; order by ; limit

Description:

The select must be a range on a primary key integer AND equal to an integer value on another indexed column. The problem appears only if you add an order by on the primary key and limit with a small value (the value depends on the number of rows in the table).

How to repeat:
CREATE TABLE `a` (
  `x` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `y` int(11) DEFAULT NULL,
  PRIMARY KEY (`x`),
  KEY `y` (`y`)
) ENGINE=MyISAM;

INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');
INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');
INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');

mysql> select * from a where x > 1 and y=10 order by x desc limit 5;
+---+------+
| x | y    |
+---+------+
| 6 |   10 |
| 5 |   10 |
| 4 |   10 |
| 3 |   10 |
| 2 |   10 |
+---+------+
5 rows in set (0.00 sec)

mysql> select * from a where x > 1 and y=10 order by x desc limit 1;
Empty set (0.00 sec)

???

then if I order by y

mysql> select * from a where x > 1 and y=10 order by y desc limit 1;
+---+------+
| x | y    |
+---+------+
| 2 |   10 |
+---+------+
1 row in set (0.00 sec)

Here the problem appears only if the limit value is < 2 but if I have 1000000 row in the table it returns empty set with limit 10, and I get the right the results if I use limit 100 (actually, it's when the optimizer uses filesort).

Suggested fix:
A workaround is to add use index in the query:

mysql> select * from a use index(PRIMARY) where x > 1 and y=10 order by x desc limit 1;
+---+------+
| x | y    |
+---+------+
| 6 |   10 |
+---+------+
1 row in set (0.00 sec)

Even when using the same index than the one normally  chosen by the optimizer it works :

mysql> select * from a use index(y) where x > 1 and y=10 order by x desc limit 1;
+---+------+
| x | y    |
+---+------+
| 6 |   10 |
+---+------+
1 row in set (0.00 sec)

Interesting is the difference with explain on the 2 queries.

mysql> explain select * from a use index(y) where x > 1 and y=10 order by x desc limit 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | a     | ref  | y             | y    | 5       | const |    5 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from a  where x > 1 and y=10 order by x desc limit 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | ref  | PRIMARY,y     | y    | 5       | const |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Thank you for a problem report. I can not repeat this with 5.1.26 though:

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `a` (
    ->   `x` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `y` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`x`),
    ->   KEY `y` (`y`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `test`.`a` (`x`, `y`) VALUES (NULL, '10'), (NULL, '10');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from a where x > 1 and y=10 order by x desc limit 5;
+---+------+
| x | y    |
+---+------+
| 6 |   10 |
| 5 |   10 |
| 4 |   10 |
| 3 |   10 |
| 2 |   10 |
+---+------+
5 rows in set (0.06 sec)

mysql> select * from a where x > 1 and y=10 order by x desc limit 1;
+---+------+
| x | y    |
+---+------+
| 6 |   10 |
+---+------+
1 row in set (0.00 sec)

What I am missing?

I did my test with the latest cluster binaries (mysql-cluster-gpl-6.2.15-linux-i686-glibc23) and then tried on 5.1.26 but I certainly missed something(certainly started the wrong server) since I am not able to reproduce in 5.1.26. I apologize for this but the problem is still on the cluster binaries which uses 5.1.23 for sql nodes.

See bugs: http://bugs.mysql.com/bug.php?id=37964 and http://bugs.mysql.com/bug.php?id=37830

Looks like a duplicate of bug #35206 (fixed in 5.1.24). Please, check.

I tested against 5.1.23rc and 5.1.24rc. The bug was solved in 5.1.24rc and is a duplicate of bug  #35206. But cluster is using a server version based on 5.1.23 for sql nodes (Server version: 5.1.23-ndb-6.2.15-cluster-gpl-log MySQL Cluster Server (GPL)).

Thank you for the feedback.

Bug is also fixed in version  mysql-5.1.24-ndb-6.3.16-telco, so you can upgrade to this version or wait next ndb-6.2* release.

So I mark this report as duplicate of bug #35206