On 11/22/2016 9:26 AM, Richard Hipp wrote: > On 11/19/16, Richard Hipp <d...@sqlite.org> wrote: >> On 11/19/16, Andy Goth <andrew.m.g...@gmail.com> wrote: >>> Is this extension documented? What is the precise behavior? Are >>> there guarantees that it will be preserved throughout future >>> versions of SQLite? When was it introduced? >> >> I thought I had documented this someplace, but now I cannot find >> where. > > Documentation added here: > https://www.sqlite.org/draft/lang_select.html#bareagg
Thanks. Sorry, I've been too busy to do it myself.
First sentence: Suggest changing "appears" to "appear" to match number
of subject ("names").
Second sentence: Suggest this alternate formulation:
A result column which contains a column name that is not within an
aggregate function and that does not appear in the GROUP BY clause (if
one exists) is called a "bare" column.
Consider this crazy thing:
CREATE TABLE x(a);
INSERT INTO x VALUES (1), (2.5), (4), (3), (2);
SELECT min(a)*avg(a), max(a)*avg(a) FROM x;
SELECT a*avg(a) FROM x;
SELECT a*avg(a), min(a)*avg(a), max(a)*avg(a) FROM x;
SELECT a*avg(a), max(a)*avg(a), min(a)*avg(a) FROM x;
The first SELECT is well-defined, even in standard SQL. It produces
2.5 and 10.0.
The second SELECT produces 5.0. Clearly it's multiplying the average
(2.5) by the last value inserted (2). Rearranging the INSERT order
suggests that it's always using the last value inserted. You made no
promises about this, and I can easily see ORDER BY or INDEXes or the
optimizer in general making an impact here, so I wouldn't rely on it.
Same goes for multiple rows having the same minimum or maximum value.
The third and fourth SELECTs hit the once-again-ambiguous case of mixing
max() and min() (there's your tongue-twister for the day). Their
a*avg(a) results are 10.0 and 2.5, respectively, suggesting the
rightmost max() or min() function determines which row is used to
compute bare columns. Do you want to document this behavior, or do you
want to reserve the right to change it later?
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
signature.asc
Description: OpenPGP digital signature
_______________________________________________ fossil-dev mailing list fossil-dev@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/fossil-dev
