SQLite handles GROUP BY and ORDER BY in basically the same way. If there is an apropirate index, then it will use this index and the rows will be returned in visitation order of this index. If, for exmaple by adding a new index or even an upgrade of the Query Planner, a different execution plan is constructed, then the order of the returned rows will "change".
Please note that GROUP BY id,o1,o2 and ORDER BY o1,o2 are DIFFERENT expressions and require reordering, which is the reason for an additional BTree step. If you need the rows to be returned in a specific order, then you must say so explicitly with an ORDER BY clause; otherwise, SQLite is free to return rows in any order that happens to be convenient for the DB Engine. Relying on the "natural" order is a common way of creating code that breaks unexpectedly. Similarly, if you need the result columns to have certain names, you must provide these via AS clauses. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von John Found Gesendet: Sonntag, 16. September 2018 10:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] The "natural" order of the query results. 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
