MySQL Bugs: #35560: order by + limit results in no returned rows

Bug #35560	order by + limit results in no returned rows
Submitted:	25 Mar 2008 20:40	Modified:	9 Apr 2008 6:38
Reporter:	Joshua Eichorn	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Linux (x86-64)
Assigned to:		CPU Architecture:	Any

Description:
This problem is seen on a newly imported table with no updates since the initial import:
SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' ORDER BY dt DESC

returns 79 rows
---
SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' ORDER BY dt DESC limit 0,1 

returns a empty set
---
 SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' limit 0,1; 
(removing the limit)

returns 1 row
---

Also note that setting the limit to values > 4 returns rows but a limit of 1,2, or 3 does not

How to repeat:
I can provide a dump of the table if needed

CREATE TABLE `yawiki_store` (
  `area` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `page` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `dt` datetime NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `body` longtext COLLATE utf8_unicode_ci NOT NULL,
  `html` longtext COLLATE utf8_unicode_ci,
  KEY `yawiki_store_area_idx` (`area`),
  KEY `yawiki_store_page_idx` (`page`),
  KEY `yawiki_store_dt_idx` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Thank you for a problem report. Please, provide a dump or, at least, EXPLAIN results for all selects.

Explains for Queries
mysql> explain SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' ORDER BY dt DESC;
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-----------------------------+
| id | select_type | table        | type | possible_keys                                                   | key                   | key_len | ref   | rows | Extra                       |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | yawiki_store | ref  | yawiki_store_area_idx,yawiki_store_page_idx,yawiki_store_dt_idx | yawiki_store_page_idx | 767     | const |   56 | Using where; Using filesort |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' ORDER BY dt DESC limit 0,1;
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys                                                   | key                   | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | yawiki_store | ref  | yawiki_store_area_idx,yawiki_store_page_idx,yawiki_store_dt_idx | yawiki_store_page_idx | 767     | const |  800 | Using where |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+

mysql> explain  SELECT * FROM yawiki_store WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' limit 0,1;
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys                                                   | key                   | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | yawiki_store | ref  | yawiki_store_area_idx,yawiki_store_page_idx,yawiki_store_dt_idx | yawiki_store_page_idx | 767     | const |   56 | Using where |
+----+-------------+--------------+------+-----------------------------------------------------------------+-----------------------+---------+-------+------+-------------+

Dump file of table showing the problem

Attachment: test.sql (text/x-sql), 87.67 KiB.

The attached file is a dump of a copy of the tables with a bunch of rows removed (its from a wiki so it was huge).  The copy was showing the same results in my testing.

Sorry, but I can not repeat the behaviour described on a test data you uploaded with recent 5.1.25 built from sources:

mysql> SELECT * FROM test WHERE area = 'HTML_AJAX' AND page = 'HomePage' AND dt <= '2008-03-25 13:09:59' ORDER BY dt DESC limit 0,1\G
*************************** 1. row ***************************
    area: HTML_AJAX
    page: HomePage
      dt: 2006-11-25 13:00:05
username: jeichorn
   title: HTML_AJAX - PEAR Package for AJAX and PHP
    note:
    body: HTML_AJAX is a PEAR package for performing AJAX operations from PHP.

If your interested in learning more, the ((Manual)) is a good place to start

* [WhatIsAJAX What is AJAX?]
* ((start|Getting-Started Guide))
* ((Faq|FAQ))
* ((Roadmap|Development Roadmap))
* ((Tutorials))
* ((InThePress|In The Press))
* ((DeveloperInfo|Developer Info))
* ((Examples))
* ((Authors))
* ((ajaxResources|Books, articles and other useful AJAX links))
* ((Projects Using HTML_AJAX))

* **((Manual))**
* **((API Documentation))**

+++ Release Notes
* ((release-0.5.0))
* ((release-0.4.1))
* ((release-0.4.0))
* ((release-0.3.4))
* ((release-0.3.2-3))
* ((release-0.3.1))
* ((release-0.3.0))

+++ Useful Links
* [http://lists.bluga.net/mailman/listinfo/html_ajax-devel HTML_AJAX mailing list]
* [http://pear.php.net/package/HTML_AJAX PEAR package description (includes bug reporting and API docs)]
* [http://bluga.net/projects/HTML_AJAX/examples/ Online version of the examples]
* [http://blog.joshuaeichorn.com/archives/category/php/html_ajax/ Josh's blog entries about HTML_AJAX]
* [http://pear.php.net/manual/en/introduction.php More Info on PEAR]

+++ Helping Out

There are many ways to get involved with the development of HTML_AJAX. The easiest way is to help improve the documentation here on this wiki.  Reporting bugs on the PEAR site or on the mailing list is also useful.  If you're a developer we're also looking for new people to help out with features and write examples.

We're also always looking for people to help test new releases. If you run a non-mainstream browser this is especially helpful.

If you want to help out with testing or development please join the [http://lists.bluga.net/mailman/listinfo/html_ajax-devel mailing list].  If you're just using HTML_AJAX feel free to join the mailing list as well. It's a great place to ask questions and give feedback.

    html: NULL
1 row in set (0.00 sec)

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

So, a bug, if any, is already fixed somehow.