On Sat, Apr 19, 2014 at 11:14 PM, foxlit <foxlit.s...@go-hero.net> wrote:
> Hi, > > I recently noticed something similar to the following behaviour: > > sqlite> .version > SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3 > sqlite> CREATE TABLE t1 (x, y); > sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0); > sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y; > 1|1 > 2|0 > sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic) > sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y; > 2|0 > 1|1 > > The second result appears to be ignoring the ORDER BY clause. Is this a > bug, or am I missing something obvious? > Bug. I created a ticket here http://www.sqlite.org/src/tktview/b75a9ca6b0499 The work-around is to add a "+" before one of the terms on the ORDER BY clause. Ex: SELECT x,y FROM t1 GROUP BY x,y ORDER BY x,+y; The problem is caused by an optimization ( http://www.sqlite.org/src/artifact/269c3e31a4?ln=4722-4732) that has been in the code since 2010-04-26 that omits the ORDER BY clause if there is an identical GROUP BY clause, since GROUP BY is (or at least was) implemented by sorting as if it were an ORDER BY. This optimization worked fine until the next generation query planner ( http://www.sqlite.org/queryplanner-ng.html) was cut over in 2013-06-26. The NGQP introduced some new ways to handle GROUP BY which made that optimization no longer valid in some circumstances - one of which you have just found. So, this is a case of two separate optimizations interfering with one another. Thanks for the bug report. Oops. Look like Dan and I entered duplicate tickets. I'll cancel one of them.... -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
