On Mon, 17 Sep 2018 06:02:37 +0000 Hick Gunter <h...@scigames.at> wrote:
> 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". Yes, of course, but you forgot about INDEXED BY clause. It will force using particular index. So, the query planner will always use exactly this index, regardless of how optimal it is and as long as GROUP BY and ORDER BY are working the same way, this gives some guarantee for the ordering without ORDER BY clause. Or my logic is wrong? Anyway, read my second response to the DRHs example. IMHO, there is some kind of misbehavior with the ORDER BY planning when ordering descending. > > 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 -- John Found <johnfo...@asm32.info> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
