Without an order by, sqlite can return the rows in any order it pleases. Likely whatever consumes the least resources. Although unlikely given your indices, it might be possible - for instance if some future micro-optimisation finds that it's quicker to read the index in reverse, then sqlite would give things in the opposite order. If you leave out a necessary order by you are very much exposing yourself to internal changes. So much so that there is a pragma reverse_unordered_selects (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects) you can use to specifically find if your app makes invalid assumptions about order.
You might have found a missed optimisation opportunity here (although there's always the tradeoff of library size & cycles to optimise vs execution cycles saved to consider). > On 16 Sep 2018, at 6:29 pm, John Found <johnfo...@asm32.info> wrote: > > Is there some relation between the indexes used in the query, the GROUP BY > fields used > and the order of the result rows, when no "ORDER BY" clause is used? > > I am asking, because I noticed, that on some queries, when I am using "ORDER > BY" the query always > use temporary b-tree for ordering, but by including the needed fields in the > "GROUP BY" clause > and removing the ORDER BY clause, the query returns the rows in the proper > order without temp b-tree. > > So, is it safe to use this implicit ordering, or this behavior can be changed > in the future versions of SQLite? > > Here is an example: > > create table A ( > id integer primary key autoincrement, > o1 integer, > o2 integer > ); > > create table B ( > Aid integer references A(id), > data text > ); > > create index idxA on A(o1 desc, o2 desc); > > insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); > insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, > "e"), (3, "f"), (3, "g"); > > -- Always uses temp b-tree for order by > select > group_concat(B.data), o1, o2 > from > A > left join > B on A.id = B.Aid > group by > A.id > order by > A.o1 desc, A.o2 desc; > > explain query plan: > id parent notused detail > 8 0 0 SCAN TABLE A > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > 58 0 0 USE TEMP B-TREE FOR ORDER BY > > > -- This one returns the rows in the needed order without ORDER BY > select > group_concat(B.data), o1, o2 > from > A indexed by idxA > left join B on A.id = B.Aid > group by A.id, A.o1, A.o2; > > explain query plan: > id parent notused detail > 7 0 0 SCAN TABLE A USING COVERING INDEX idxA > 18 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > > > -- But if I add ORDER BY it still begins to use temp b-tree > -- regardless that it does not change the order. > select > group_concat(B.data), o1, o2 > from > A indexed by idxA > left join B on A.id = B.Aid > group by A.id, A.o1, A.o2 > order by A.o1 desc, A.o2 desc; > > explain query plan: > 8 0 0 SCAN TABLE A > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > 60 0 0 USE TEMP B-TREE FOR ORDER BY > > > All the above queries, returns the same result rows in the same order: > > group_concat(B.data) o1 o2 > NULL 5 300 > f,g 3 200 > c,d,e 2 50 > a,b 1 100 > > > > > -- > John Found <johnfo...@asm32.info> > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
