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] Miguel Solorzano
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] Miguel Solorzano
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