> -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Petite Abeille > Sent: 12 October 2011 00:15 > > I wonder how much of that feature is intentional vs. accidental. To me it > looks more like an implementation detail leak being post-rationalized as a > feature. Just my 2ยข though.
I thought so too, but the behaviour is documented under the select statement: http://www.sqlite.org/lang_select.html#resultset Under " 3. Generation of the set of result rows." Third bullet, third paragraph: "If the SELECT statement is an aggregate query with a GROUP BY clause, then each of the expressions specified as part of the GROUP BY clause is evaluated for each row of the dataset. Each row is then assigned to a "group" based on the results; rows for which the results of evaluating the GROUP BY expressions are the same are assigned to the same group. For the purposes of grouping rows, NULL values are considered equal. The usual rules for selecting a collation sequence with which to compare text values apply when evaluating expressions in a GROUP BY clause. The expressions in the GROUP BY clause do not have to be expressions that appear in the result. The expressions in a GROUP BY clause may not be aggregate expressions. If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression. If the result of evaluating the HAVING clause is false, the group is discarded. If the HAVING clause is an aggregate expression, it is evaluated across all rows in the group. If a HAVING clause is a non-aggregate expression, it is evaluated with respect to an arbitrarily selected row from the group. The HAVING expression may refer to values, even aggregate functions, that are not in the result. Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row. Each group of input dataset rows contributes a single row to the set of result rows. Subject to filtering associated with the DISTINCT keyword, the number of rows returned by an aggregate query with a GROUP BY clause is the same as the number of groups of rows produced by applying the GROUP BY and HAVING clauses to the filtered input dataset. " But then again, it could of course still be something that was documented after the fact. In any case, although I can see a certain advantage as explained earlier by Igor (less SQL code, very small performance gain) when the a column contains the same value for all rows in the result set, I think that the danger of getting a wrong result (as from what you would expect) far outweighs the advantage. The best would be a choice now that the feature is there and expected by some. Does anyone from SQLite Development have an opinion on this? /Frank Missel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
