-----Original Message-----
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
Behalf Of Fredrik Larsen
Sent: Saturday, 21 September, 2019 08:12
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work
as expected
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
