On Wed, Nov 23, 2011 at 10:24:12AM -0800, Wiktor Adamski scratched on the wall:
> > ? In the first query, there is an aggregate in the result set, so an
> > ? implicit GROUP BY is used. ?The ORDER BY is meaningless, but not an
> > ? error (and could be more easily written "ORDER BY 1"; see below).
>
> The order is not meaningless.
In your last example, you did not have an explicit GROUP BY clause.
You were expecting an implicit GROUP BY, which *always* returns
exactly one row-- even for an empty table (as your earlier example
showed-- that blank line is a one-row NULL result).
I would assert attempting to order a one-row result is a meaningless
operation.
I'm specifically using the word "meaningless", and not "incorrect."
It isn't an error, it just has no bearing on the end result.
Ordering a multi-row result does have meaning-- including ordering by
the result of an aggregate function. And this is allowed. However,
the only way to get a multi-row grouped result is with an explicit
GROUP BY. If the statement had an explicit GROUP BY, the aggregate
in the ORDER BY would not be an error.
> aggregate in order by isn't allowed it should return an error.
It *is* allowed. The error is not the fact there was an aggregate
in an ORDER BY. The error is that there was an aggregate in a query
that had no groups. This whole issue is about when an implicit group
is created, and when it is not. It is not about the ability to put
an aggregate into an ORDER BY-- that is absolutely allowed.
> It would be nice if database behaved consistently.
It does. The behavior is easy to define. A query with any type of
aggregate requires groups. You can create groups with an explicit
GROUP BY statement **OR** you can have the database create an
implicit GROUP BY over the entire result set. However, an implicit
GROUP BY will only be created if there is an aggregate function in
the result set (that is, between SELECT and FROM).
Aggregates *are* allowed in non-result expressions, such as ORDER BY
(as your examples show). However, this is only allowed if there is
an explicit GROUP BY clause in the query, as a lone aggregate in a
non-result expression will not trigger an implicit GROUP BY. While
the docs URL I sent out before doesn't spell it out quite that
explicitly, it seems clear enough to me.
Your last example has no explicit GROUP BY, nor does it have any
aggregate in the result set. Therefore, the query has no groups,
and attempting to use an aggregate function in a query without any
groups is an error-- as reported.
However, as your other examples show, any explicit GROUP BY statement
will allow the aggregate to be processed correctly. This includes a
trivial GROUP BY statement, such as "GROUP BY 1=1", which is (almost)
equivalent to an implicit whole-result GROUP BY ("GROUP BY 1" alone
is not always a trivial grouping due to the indexing short-cut).
Of course, having a trivial grouping across the whole result set will
never return more than one row (unlike an implicit grouping, an empty
table will return no rows on an empty table), which brings us back to
the wisdom of trying to order one row.
> We already know that MS SQL and Firebird do. Same with MySQL.
> I'm not sure, but I think I'v tried also postgres with same result.
That might be true, but I'm not sure that's a very strong point.
Unless someone can point out a standards issue, just because other
databases do it one way doesn't make them any more right or wrong.
And yes, PostgreSQL will trigger an implicit GROUP BY if an aggregate
function appears anywhere in the query, not just in the result set.
> I'm sure that in sqlite it was missed and
> it is not an intentional behaviour.
Given the way the docs are phrased, I'm sure it is intentional. I
have no idea what the SQL standard says (my guess is that it doesn't,
and this is a gray area), but I understand the logic behind the
decision.
Triggering (or not) an automatic grouping by a non-result aggregate
is one of those things that doesn't strike me as "right" or "wrong",
it simply is a question of if you want to allow nonsense queries
that aren't technically "wrong", but don't make a lot of sense, of if
you want to guide the user away from queries that are very likely not
doing what the user thinks they are doing.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
