SQLite
View Ticket
Not logged in
Ticket UUID: 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: (text/x-fossil-wiki)
The SELECT statement at the end of the following SQL generates results 
in the wrong order.

<blockquote><verbatim>
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;
</verbatim></blockquote>

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.