MySQL Bugs: #971: ORDER BY DESC doesn't return correct num of rows with BDB and an indexed column

Bug #971	ORDER BY DESC doesn't return correct num of rows with BDB and an indexed column
Submitted:	31 Jul 2003 16:44	Modified:	2 Sep 2003 23:33
Reporter:	Deepak Giridharagopal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13, 4.0.14	OS:	Linux (Linux, Solaris)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
ORDER BY DESC doesn't work correctly with BDB and an indexed column. It doesn't return the correct number of rows...it truncates the result set.

Here's how it went down on my box:

##### Create the table

mysql> create table test ( x INT NOT NULL, INDEX(x) ) TYPE=BDB;
Query OK, 0 rows affected (0.06 sec)

#### Insert 10 dummy rows

mysql> insert into test values(1);
mysql> insert into test values(2);
mysql> insert into test values(3);
mysql> insert into test values(4);
mysql> insert into test values(5);
mysql> insert into test values(6);
mysql> insert into test values(7);
mysql> insert into test values(8);
mysql> insert into test values(9);
mysql> insert into test values(10);

#### Now select a range of rows

mysql> select * from test where x <= 10 and x >= 7; 
+----+
| x  |
+----+
|  7 |
|  8 |
|  9 |
| 10 |
+----+
4 rows in set (0.00 sec)

#### Select the same range, and order by x

mysql> select * from test where x <= 10 and x >= 7 order by x;
+----+
| x  |
+----+
|  7 |
|  8 |
|  9 |
| 10 |
+----+
4 rows in set (0.00 sec)

#### Select the same range, and order by x DESC

mysql> select * from test where x <= 10 and x >= 7 order by x desc; 
+---+
| x |
+---+
| 9 |
| 8 |
| 7 |
+---+
3 rows in set (0.00 sec)

####
You'll notice that the last query, the one using an ORDER BY DESC clause, returns a different number of rows that the same exact query without the DESC.
This doesn't seem correct.

This behaviour doesn't seem to come up if you use InnoDB or MyISAM instead of BDB. If you use BDB, the bug doesn't show up if you remove the INDEX on the column.

How to repeat:
create table test ( x INT NOT NULL, INDEX(x) ) TYPE=BDB;
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);
insert into test values(6);
insert into test values(7);
insert into test values(8);
insert into test values(9);
insert into test values(10);
select * from test where x <= 10 and x >= 7;
select * from test where x <= 10 and x >= 7 order by x;
select * from test where x <= 10 and x >= 7 order by x desc;

These last 3 queries ostensibly should return the same number of rows, but they don't. I've done this on a 4.0.13 server running on Solaris x86 and a 4.0.14 server on Linux x86.

Thank you for this excelent test case.

Thank you for this excelent test case.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

I ran into the same type of problem in version 4.0.13.  A table containing log entries was being sorted DESC by [ENTRYDATE] and then by [LOGID].  [LOGID] was the primary key, and [ENTRYDATE] wasn't indexed.  The result was that users were successfully entering logs, but they didn't show up in the history, which displays the most recently entered logs first.  But on another report that shows the logs in order of entry, they were all there.  Creating an index on [ENTRYDATE] fixed the problem, but we were baffled as to why it was necessary.