To clarify; GROUP-BY does not really have ordering, but in the SQLite implementation, GROUP-BY and ORDER-BY is very closely related as expected, and it is possible to set a GROUP-BY direction in code (it is default 0 -> ASC). So thats what I did. Also, some other modifications very required to stop SQLite from assuming to much about GROUP-BY queries.
Fredrik On Sat, Sep 21, 2019 at 4:12 PM Fredrik Larsen <frel...@gmail.com> wrote: > 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
