SQLite

View Ticket
Login
2014-12-04
21:54
Make sure that a DISTINCT query with an ORDER BY works correctly even if it uses a descending index. Fix for ticket [c5ea805691bfc4204b1cb9e]. (check-in: 0d3aef97 user: drh tags: trunk)
21:54 Fixed ticket [c5ea8056]: Inverted sort order when using DISTINCT and a descending index plus 5 other changes (artifact: af5ba544 user: drh)
21:29 New ticket [c5ea8056]. (artifact: 9fb2d923 user: drh)

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