The dialogue from the stackoverflow discussion shows this quite clearly. "The code for looping over an index goes backwards only when needed. For implementing GROUP BY itself, going backwards is never needed, so it is never tried.
It is possible that a future SQLite version might add code to the GROUP BY implementation to check for this special case. Make the index DESC on x. – CL. 18 hours ago Already tried some variations of DESC in index, same result as before. Declerative descriptions are nice until you hit some annoying thing that the fancy optimizer don't handle very well. If only I had a bit more control over the query-planner – frelars 18 hours ago " -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Freitag, 20. September 2019 11:12 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected On Thu, Sep 19, 2019 at 6:15 PM Hick Gunter <h...@scigames.at> wrote: > -----Ursprüngliche Nachricht----- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Fredrik Larsen > Gesendet: Donnerstag, 19. September 2019 17:29 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not > work as expected ... > Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows > traversal both ways. You can see this if you remove GROUP_BY. > ... > True and nothing new, but not the point. > > After doing GROUP BY x over the covering index, the result rows would > be returned by x ASC. There is no index on the rowset returned by the GROUP BY, as the rows only > exist one at a time. Therefore, the only way to get them into ORDER BY X DESC is to sort them. > But who says the GROUP BY must return rows in ASCending order? A lot of us "oldies" of this ML well know the order is arbitrary and subject to change w/o an explicit ORDER BY. So the GROUP BY is allowed, AFAIK, to return rows in DESCending order just the same. And to do so efficiently as Fredrik points out, since indexes (or indices, I never know) work equally well in both directions. In fact, it could return rows in arbitrary / random order too! The query-planner does see the ORDER BY that follows the GROUP BY after all, so it could well decide to group in DESCending order, thus avoiding the ordering completely, like it already does for ASCending. This would be a great optimisation, and from 30,000ft, it does indeed seem like a "simple" one compared to all the advanced optimisations already implements, as Fredrik mentioned. I might even say that it looks like a "low-hanging-fruit", if I dared :). Dunno, perhaps GROUP BY has some requirement an ordering, or GROUP BY impls somehow can't easily work "in reverse", I'm no expert of the code. I wish the experts would chime in. Too often we never hear any rational for doing or not doing things. This is a "users" list and there's no "dev" list. I wish more was shared about the internal structures, impls, etc... explaining why something is harder to implement that it sounds. Oh well... --DD _______________________________________________ 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
