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
