This is the much discussed and misunderstood feature that enables SQLite to process statements like
SELECT A, B, MAX(C) FROM TABLE GROUP BY B; and return the (or one of the) value(s) of A associated with the maximum value of C within each group of B in just one full table scan. By extension, this will allow you to write and execute SELECT SUM(A),B FROM TABLE GROUP BY B HAVING A<>0; (i.e. certainly omit groups with only zero values of A and randomly omit groups containing at least one zero value of A) when you possibly intended to write SELECT SUM(A),B FROM TABLE WHERE A<>0 GROUP BY B; which includes a WHERE clause that probably contributes only CPU Cycles to the result (unless your original statement contains a count() expression). -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von John McKown Gesendet: Freitag, 29. April 2016 20:03 An: SQLite mailing list Betreff: Re: [sqlite] Illegal SQL not rejected On Fri, Apr 29, 2016 at 12:41 PM, Igor Korot <ikorot01 at gmail.com> wrote: > Hi, > > On Fri, Apr 29, 2016 at 1:28 PM, John McKown > <john.archie.mckown at gmail.com> wrote: > > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder > > <j.roder at wintoncapital.com> > > wrote: > > > >> Hi, > >> It seems like a too obvious omission to not be intentional. But I > >> wonder why a query like > >> > >> SELECT SUM(A) > >> FROM TABLE > >> GROUP BY B > >> HAVING A <> 0 > >> > >> Is not rejected. MS SQL server gives you this error message in this > case: > >> > >> Column ?A? is invalid in the HAVING clause because it is not > >> contained > in > >> either an aggregate function or the GROUP BY clause. > >> > > > > I agree that it just looks _wrong_. And PostgreSQL certainly > > complains about it. > > This is weird because A is part of sum(A), which IS aggregate function > call. > Or am I missing something? > ?It is the syntax. PostgreSQL rejects SELECT SUM(A) FROM TABLE GROUP BY B HAVING A<>0; but accepts SELECT SUM(A) FROM TABLE GROUP BY B HAVING SUM(A) <>0; ?SQLite is processing the first one as if would the second, as best as I can tell. The PostgreSQL documentation states it as I am used to seeing: <quote> HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in conditionmust unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns. </quote>? The SQLite documentation states (point 3) <quote> If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression <http://sqlite.org/lang_expr.html#booleanexpr>. 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. </quote> ?So, it turns out that SQLite is documented as accepting the statement. So this is not a "bug", per se. But basing a resultant row on "evaluated with respect an arbitrarily selected row from the group" seems "un-useful".? > Thank you. > > -- The unfacts, did we have them, are too imprecisely few to warrant our certitude. Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
