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
