SQLite

View Ticket
Login
2014-10-11
02:12 Fixed ticket [ba7cbfae]: Wrong output order when a DESC index and GROUP BY and ORDER BY. plus 5 other changes (artifact: 7542bf31 user: drh)
02:12
Make sure that a GROUP BY that also implements an ORDER BY scans the table in the correct order. Fix for ticket [ba7cbfaedc7e6]. (check-in: 7a32fdfd user: drh tags: trunk)
2014-10-10
20:52
Account for the ASC/DESC properties of ORDER BY expressions when using the same index for GROUP BY and ORDER BY. Candidate fix for [ba7cbfaedc]. (Closed-Leaf check-in: 2a957396 user: dan tags: experimental)
19:58 New ticket [ba7cbfae] Wrong output order when a DESC index and GROUP BY and ORDER BY.. (artifact: 8a70e91f user: drh)

Ticket Hash: ba7cbfaedc7e6d962d801c1749209446a2bbc243
Title: Wrong output order when a DESC index and GROUP BY and ORDER BY.
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-10-11 02:12:59
Version Found In: 3.8.6
User Comments:
drh added on 2014-10-10 19:58:27:

The SELECT statement at the end of the following SQL generates results in the wrong order.

CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
CREATE INDEX ix1 ON t1(x DESC);
SELECT * FROM t1 GROUP BY x ORDER BY x;

This problem arises when there is a DESC index which is used to determine the output order and the query has identical GROUP BY and ORDER BY clauses and define ASC output order. The problem seems to have been introduced with the new query planner in version 3.8.0, 2013-08-26.