Your last sentence got me thinking. So I downloaded the source, modified the ordering of the GROUP-BY expression to match ORDER-BY and it works! This will offcourse only work if the GROUP-BY and ORDER-BY matches generally expect for the direction. This fix only improves performance for relevant cases and keeps other cases unaffected. Not sure if I introduced some subtle bugs with this modification, but my test-cases runs fine.
Fredrik On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf <kmedc...@dessus.com> wrote: > >We can observe GROUP BY works ASCending only as of now. Why it can't work > >DESCending to avoid ordering, that's a different question. > >From https://www.sqlite.org/lang_select.html we can observe that > >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr > >followed by optional COLLATE and ASC/DESC terms. > > The GROUP BY clause does not imply ordering. The fact that the output is > ordered is an implementation detail -- the grouping could be implemented by > a hash table, in which case the output would be ordered by hash value, for > instance. All that the expression in a GROUP BY does is determine the > groupings, and therefore the expression is limited to a comparison > compatible expression. For example, you can GROUP BY x COLLATE NOCASE > which implies that the groups are formed using case insensitive comparisons > of x. The ORDER BY clause determines the output ordering. > > You will note that if you do the following: > > create table x(x,y); > create index ix on x(x desc, y); > select x, someaggregate(y) from x group by x order by x desc; > > then ix will be used as a covering index (which is good) however the group > by x is treated as an ordering expression, not as simply a grouping > expression. > > In fact the code that implements the group by does indeed (perhaps > erroneously) treat the group by expression as implying order, since it will > traverse the covering index in reverse order so that the output from GROUP > BY is in ascending order, and add an extra sort to do the ORDER BY. > > That means the GROUP BY code generator is already capable of traversing > the selected index in reverse order when necessary. It appears that the > optimizer however does not recognize that the "desc" attribute from the > order by can be "pushed down" into the GROUP BY (which really is ordering > as an implementation detail) thus eliminating the ORDER BY processing > entirely. > > Note that you cannot specify that the GROUP BY is ordering -- it will not > accept the ASC or DESC keywords (which is correct), and this should not be > changed, however, treating it as being ordering when it is not might > perhaps be a defect ... > > > > _______________________________________________ > 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
