SQLite

View Ticket
Login
Ticket Hash: 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:

The file SELECT statement in the following SQL returns results in descending order instead of the ascending order requested:

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;

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].