SQLite
View Ticket
Not logged in
Ticket UUID: c5ea805691bfc4204b1cb9e9aa0103bd48bc7d34
Title: Inverted sort order when using DISTINCT and a descending index
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-12-04 21:54:50
Version Found In: 3.8.0
User Comments:
drh added on 2014-12-04 21:29:33: (text/x-fossil-wiki)
The file SELECT statement in the following SQL returns results in descending
order instead of the ascending order requested:

<blockquote><verbatim>
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(3),(1),(5),(2),(6),(4),(5),(1),(3);
CREATE INDEX t1x ON t1(x DESC);
SELECT DISTINCT x FROM t1 ORDER BY x ASC;
</verbatim></blockquote>

The problem only comes up in a DISTINCT query when using a descending
index.  The problem seems to have been introduced as part of the NGQP in
SQLite version 3.8.0 circa 2013-06-26, checkin [0fe31f60cadc5fe5a9d].