On Mon, 5 Jan 2015 06:39:42 +0000
Hick Gunter <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
