On Mon, 5 Jan 2015 06:39:42 +0000 Hick Gunter <h...@scigames.at> wrote:
> This is completely legal and well defined. > > HAVING is applied to the RESULT set of a SELECT. I beg to differ. It's both invalid SQL and (therefore) undefined. Furthermore, it's illogical. Consider: create table T (a int, b int, c int); insert into T values (1, 2, 1); select count(*), a from T group by a having b > 0; Surely "select count(*), a from T" produces 1 row, count(*) a -------- - 1 1 but what does "having b > 0" mean if "HAVING is applied to the RESULT set of a SELECT"? There is no B! If ad argumentum we say B refers to the B in the table, the question remains: how to interpret the having clause? Is it TRUE If 1. there exists a row for a given value of A for which B > 0? 2. all rows for a given A have B > 0? It so happens (per http://sqlfiddle.com/#!5/34e8d/1) that sqlite accepts the query and uses interpretation #2. But that is arbitrary: HAVING is not a FORALL or EXISTS quantifier. It has a single, clunky job: to test aggregates. Most SQL DBMSs reject the query outright. As well they should, because it is nonsensical. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users