MySQL: order by and limit gives wrong result

Question

MySQL ver 5.1.26

I'm getting the wrong result with a select that has where, order by and limit clauses. It's only a problem when the order by uses the id column.

I saw the MySQL manual for LIMIT Optimization

My guess from reading the manual is that there is some problem with the index on the primary key, id. But I don't know where I should go from here...

Question: what should I do to best solve the problem?

Works correctly:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY id DESC ;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
| 1334 | 2010-05-06 08:05:25 |
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
3 rows in set (0.00 sec)

WRONG result when limit added! Should be the first row, id - 1336
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
1 row in set (0.00 sec)

Works correctly:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC ; 
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
| 1334 | 2010-05-06 08:05:25 |
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
3 rows in set (0.01 sec)

Works correctly with limit:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC limit 1;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
+------+---------------------+
1 row in set (0.01 sec)

Additional info:
explain SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1;
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys                        | key                                  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | billing_invoices | range | index_billing_invoices_on_account_id | index_billing_invoices_on_account_id | 4       | NULL |    3 | Using where |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+

Added SHOW CREATE TABLE billing_invoices result:

Table -- billing_invoices
Create Table --
CREATE TABLE `billing_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `invoice_date` date NOT NULL,
  `prior_invoice_id` int(11) DEFAULT NULL,
  `closing_balance` decimal(8,2) NOT NULL,
  `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `monthly_invoice` tinyint(1) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_billing_invoices_on_account_id` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1337 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Added more:

I now see that on my development machine, everything is working correctly. That machine has version VERSION() of 5.1.26-rc-log

On my production machine, where the problem is, I see that VERSION() returns 5.1.26-rc-percona-log

So at this point, I'm thinking the problem is with the percona software?

Added more:

At this point, I'm going to consider it a bug in the Percona InnoDB driver. I've put a question to their forum. As an immediate work-around, I'm going to order by created_at. I will also investigate upgrading the db on my system and see if that helps.

My thanks to Rabbott and mdma for their help. I also appreciate the help that I'm not doing something silly, this really is a problem.

Answer 1

Could be this bug that was never resolved for your updated version? http://bugs.mysql.com/bug.php?id=31001

I am running 5.1.42 locally. I copy and pasted your queries from above and am getting all the correct results.. Whether it be the bug mentioned above or not, it sounds like a bug, and it appears to have been fixed in a more recent release than yours..

Answer 2

Seems peculiar, maybe a bug? As a workarount maybe you can make the selection explicit - use a subquery to select the MAX(id) and filter on that in a WHERE clause. E.g.

SELECT id, created_at FROM billing_invoices 
   WHERE id IN (SELECT MAX(id) FROM billing_invoices WHERE account_id=5)