Bug #24067 No rows returned with ORDER BY column DESC LIMIT 1
Submitted: 8 Nov 2006 0:25 Modified: 21 Feb 2007 16:46
Reporter: David Levner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:Linux (Fedora 5)
Assigned to: CPU Architecture:Any
Tags: DESC

[8 Nov 2006 0:25] David Levner
Description:
A SQL query with DESC should return three rows but returns none. Omit DESC and the query returns the correct rows. This is similar, but not identical, to closed bug #971.

The table is BDB. The SQL query's WHERE clause refers to a column which is part of a key.

I found the problem using the perl DBI package and have reproduced it with the mysql command line program.

How to repeat:
# The following query returns zero rows returned.

mysql> SELECT name,tid_created,tid_obsoleted FROM committeesdev WHERE (committee_id = 2) AND (tid_created <= 19) ORDER BY tid_created DESC;
Empty set (0.00 sec)

# Remove DESC and three rows are returned.

mysql> SELECT name,tid_created,tid_obsoleted FROM committeesdev WHERE (committee_id = 2) AND (tid_created <= 19) ORDER BY tid_created;
+------+-------------+---------------+
| name | tid_created | tid_obsoleted |
+------+-------------+---------------+
| Top  |          17 |            18 |
| Top  |          18 |            19 |
| Top  |          19 |          NULL |
+------+-------------+---------------+
3 rows in set (0.00 sec)

# Removing "(tid_created <= 19)" from the WHERE clause also causes the bug to go away.

mysql> SELECT name,tid_created,tid_obsoleted FROM committeesdev WHERE (committee_id = 2) ORDER BY tid_created DESC;
+------+-------------+---------------+
| name | tid_created | tid_obsoleted |
+------+-------------+---------------+
| Top  |          19 |          NULL |
| Top  |          18 |            19 |
| Top  |          17 |            18 |
+------+-------------+---------------+
3 rows in set (0.00 sec)

# Here's the full table description:

mysql> describe committeesdev;
+-----------------------------+---------------------+------+-----+---------+-------+
| Field                       | Type                | Null | Key | Default | Extra |
+-----------------------------+---------------------+------+-----+---------+-------+
| committee_id                | int(10) unsigned    | NO   | PRI | NULL    |       |
| tid_obsoleted               | int(10) unsigned    | YES  |     | NULL    |       |
| when_created                | datetime            | NO   |     | NULL    |       |
| tid_created                 | int(10) unsigned    | NO   | PRI | NULL    |       |
| when_obsoleted              | datetime            | YES  |     | NULL    |       |
| quorum_equal_ok             | tinyint(3) unsigned | NO   |     | NULL    |       |
| status                      | tinyint(3) unsigned | NO   |     | 1       |       |
| term                        | varchar(32)         | NO   |     | NULL    |       |
| quorum_varies               | tinyint(3) unsigned | NO   |     | NULL    |       |
| email                       | varchar(80)         | NO   |     | NULL    |       |
| abbr                        | varchar(8)          | NO   |     | NULL    |       |
| next_proposal_number        | int(10) unsigned    | NO   |     | NULL    |       |
| pass_threshold_equal_ok     | tinyint(3) unsigned | NO   |     | NULL    |       |
| quorum_includes_abstentions | tinyint(3) unsigned | NO   |     | NULL    |       |
| name                        | varchar(50)         | NO   | MUL | NULL    |       |
| pass_threshold_fraction     | double unsigned     | NO   |     | NULL    |       |
| pass_threshold_varies       | tinyint(3) unsigned | NO   |     | NULL    |       |
| parent_committee_id         | int(10) unsigned    | NO   |     | NULL    |       |
| quorum_fraction             | double unsigned     | NO   |     | NULL    |       |
| why_inactivated             | text                | YES  |     | NULL    |       |
| voting_manager_role_id      | int(10) unsigned    | NO   |     | NULL    |       |
| voting_manager_committee_id | int(10) unsigned    | NO   |     | NULL    |       |
| listserv                    | varchar(100)        | YES  |     | NULL    |       |
| web_page                    | varchar(100)        | YES  |     | NULL    |       |
| permanent                   | tinyint(3) unsigned | YES  |     | 0       |       |
| description                 | varchar(255)        | YES  |     | NULL    |       |
+-----------------------------+---------------------+------+-----+---------+-------+
26 rows in set (0.04 sec)

# Here's the SQL I used to create this table:

committeesdev table CREATE TABLE committeesdev (committee_id int unsigned NOT NULL, tid_obsoleted int unsigned, when_created datetime NOT NULL, tid_created int unsigned NOT NULL, when_obsoleted datetime, quorum_equal_ok tinyint unsigned NOT NULL, status tinyint unsigned NOT NULL DEFAULT 1, term varchar(32) NOT NULL, quorum_varies tinyint unsigned NOT NULL, email varchar(80) NOT NULL, abbr varchar(8) NOT NULL, next_proposal_number int unsigned NOT NULL, pass_threshold_equal_ok tinyint unsigned NOT NULL, quorum_includes_abstentions tinyint unsigned NOT NULL, name varchar(50) NOT NULL, pass_threshold_fraction double unsigned NOT NULL, pass_threshold_varies tinyint unsigned NOT NULL, parent_committee_id int unsigned NOT NULL, quorum_fraction double unsigned NOT NULL, why_inactivated text, voting_manager_role_id int unsigned NOT NULL, voting_manager_committee_id int unsigned NOT NULL, listserv varchar(100), web_page varchar(100), permanent tinyint unsigned DEFAULT 0, description varchar(255), PRIMARY KEY(committee_id,tid_created), UNIQUE KEY committee(name,tid_created), KEY name(name,when_obsoleted)) TYPE=BDB
[8 Nov 2006 2:02] MySQL Verification Team
Thank you for the bug report. Your server version is pretty older, could
you please update it and try again, if you still get the same issue provide
a dump file with table definition plus insert data commands to try on
our side?. Thanks in advance.
[9 Dec 2006 0:01] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Dec 2006 4:01] David Levner
I upgraded to 5.0.27 and cannot reproduce the problem. Thanks for the upgrade suggestion--it seems to have worked. This bug report can be closed.
[12 Dec 2006 10:01] MySQL Verification Team
Thank you for the feedback.
[21 Feb 2007 16:31] RAHARINJATO Voara Rado
Hello,

I have seen this bug with my server (5.0.17).
I upgraded to 5.0.27 but I always have the problem and sometimes it works fine.

Could you tell me how to open again a bug report that is closed ?
[21 Feb 2007 16:46] David Levner
Raharinjato,

I don't know how you can reopen a bug report that has been closed. I don't think it's appropriate for me to reopen this bug report because the bug is not occurring on my system any more.

My suggestion is that you open a new bug report and refer to the number of this report. Good luck!

David Levner