Re: [HACKERS] We are not following the spec for HAVING without

Tom Lane wrote:
>
> Would those of you with access to other DBMSes try this:
>
> create table tab (col integer);
> select 1 from tab having 1=0;
> select 1 from tab having 1=1;
> insert into tab values(1);
> insert into tab values(2);
> select 1 from tab having 1=0;
> select 1 from tab having 1=1;
>
> I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows

Not that this means much, but I'll mention it for the sake of
completeness: SQLite 3.0.8 disallows all of the above SELECT
statements:

sqlite> create table tab (col integer);
sqlite> select 1 from tab having 1=0;
SQL error: a GROUP BY clause is required before HAVING
sqlite> select 1 from tab having 1=1;
SQL error: a GROUP BY clause is required before HAVING
sqlite> insert into tab values(1);
sqlite> insert into tab values(2);
sqlite> select 1 from tab having 1=0;
SQL error: a GROUP BY clause is required before HAVING
sqlite> select 1 from tab having 1=1;
SQL error: a GROUP BY clause is required before HAVING

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/