Re: GROUP BY

On 19 May, 15:20, "V.J. Kumar" <vjkm..._at_gmail.com> wrote:
>
> > SELECT 1
> > FROM r
> > GROUP BY ();
>
> > returns a single tuple even if r is empty.
>
> What SQL would that be? Oracle SQL returns zero rows (as it is supposed
> to according to '92 standard), MS SQL Express does not even understand
> the expression and returns a syntax error.
>

My bad. Microsoft SQL Server 2005 doesn't support the empty grouping set so what I actually tested was the following query:

CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;

According to ISO/IEC 9075-2:2003 section 7.10 the HAVING query without GROUP BY is equivalent to:

SELECT 1 FROM r GROUP BY () HAVING 1=1;

Microsoft SQL Server returns 1 row for the above, but this is wrong. When I test the same query in MySQL 5.0, Mimer SQL 9.2 and Oracle 10g all three products return zero rows.

--
David Portas