What is the correct result for this query?

I came across this puzzle in the comments here

CREATE TABLE r (b INT);

SELECT 1 FROM r HAVING 1=1;

SQL Server and PostgreSQL return 1 row.

MySQL and Oracle return zero rows.

Which is correct? Or are both equally valid?

Per the standard:

SELECT 1 FROM r HAVING 1=1

means

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

Citation ISO/IEC 9075-2:2011 7.10 Syntax Rule 1 (Part of the definition of the HAVING clause):


Let HC be the `. Let TE be the that
immediately contains HC. If TE does not immediately contain a
, then “GROUP BY ()” is implicit. Let T be the
descriptor of the table defined by the GBC immediately
contained in TE and let R be the result of GBC.

Ok so that much is pretty clear.

Assertion: 1=1 is true search condition. I will provide no citation for this.

Now

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

is equivlent to

SELECT 1 FROM r GROUP BY ()

Citation ISO/IEC 9075-2:2011 7.10 General Rule 1:


The is evaluated for each group
of R. The result of the is a grouped
table of those groups of R for which the result of the
is True.

Logic: Since the search condition is always true, the result is R, which is the result of the group by expression.

The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY CLAUSE)


1) If no is specified, then let T be the result of the preceding ; otherwise, let T be the result of the preceding .


2) Case:


a) If there are no grouping columns, then the result of
the is the grouped table consisting of T as its only group.

Thus we can conclude that

FROM r GROUP BY ()

results in a grouped table, consisting of one group, with zero rows (since R is empty).

An excerpt from the General Rules of 7.12, which defines a Query Specification (a.k.a a SELECT statement):


1) Case:


a) If T is not a grouped table, then [...]


b) If T is a grouped table, then


Case:


i) If T has 0 (zero) groups, then let TEMP be an empty table.


ii) If T has one or more groups, then each is applied
to each group of T yielding a table TEMP of M rows, where M is the
number of groups in T. The i-th column of TEMP contains the values
derived by the evaluation of the i-th . [...]


2) Case:


a) If the DISTINCT is not specified, then the
result of the is TEMP`.

Therefore since the table has one group, it must have one result row.

Thus

SELECT 1 FROM r HAVING 1=1

should return a 1 row result set.

Q.E.D.

StackExchange Database Administrators Q#33760, answer score: 19