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
