On Wed, Nov 23, 2011 at 08:17:17AM -0800, Wiktor Adamski scratched on the wall: > SQLite version 3.7.9 2011-11-01 00:52:41 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t(a);
> sqlite> select avg(a) from t order by avg(a); > -- order by aggregate possible > sqlite> select 1 from t order by a; > -- order by column not in result possible > sqlite> select 1 from t group by 1 order by avg(a); > -- order by aggregate not in result possible > sqlite> select 1 from t order by avg(a); > -- should be possible > Error: misuse of aggregate: avg() Not a bug. As documented (kind of): http://sqlite.org/lang_select.html#resultset Also a non-sense query. The core issue is when an aggregate can be used without a GROUP BY clause. Normally the use of an aggregate requires a GROUP BY clause so it knows *what* to aggregate. There is a special exception that if an aggregate appears IN THE RESULT SET, then an implied group is made over the whole data set. However, any query with an implied group will always return exactly one row. This makes any kind of ORDER BY statement meaningless. In the case of the last query, the ORDER BY is doubly meaningless, as your asking the result to be ordered by an expression that will always be the same for every row. 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 second query has no aggregates. The third query has an explicit GROUP BY, allowing aggregate expressions. The final expression has an aggregate, but no GROUP BY, nor any aggregate in the result set. It is an invalid query, which is fine, because it is also a nonsense query. Get rid of the ORDER BY statement and you'll get the same single-row result. It should also be pointed out that the third query is likely not doing what you think it is doing. In this specific case, the GROUP BY 1 will provide the same result as an implied, whole-dataset grouping. That is not because of the "1" in the GROUP BY clause, however, but the "1" in the SELECT clause. Literal integers passed to GROUP BY or ORDER BY are assumed to be column indexes, not literal values. "GROUP BY 1" means "group by the first column", not "group by the literal value '1'". Now it happens that in this specific query the first column is a literal value '1', resulting in a GROUP BY with a static, literal value. That, in turn, results in a single group across all rows, returning exactly one row, just as an implied GROUP BY does. This is not universally true, however. You have no idea how many rows a "GROUP BY 1" statement may return unless you know the expression used to compute the first column of the result set. -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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
