I came across this puzzle in the comments here
CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1;
Which is correct? Or are both equally valid?
Per the standard:
Citation ISO/IEC 9075-2:2011 7.10 Syntax Rule 1 (Part of the definition of the HAVING clause):
Ok so that much is pretty clear.
is equivlent to
Citation ISO/IEC 9075-2:2011 7.10 General Rule 1:
Logic: Since the search condition is always true, the result is
The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY CLAUSE)
Thus we can conclude that
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):
Therefore since the table has one group, it must have one result row.
should return a 1 row result set.
When there is a
... which should group all rows of the table into one group (even if the table has no rows at all - it's still one group of 0 rows) and return 1 row. The
From a different angle, how many rows should a query like this return?
One, zero or "zero or one, depending on if the table is empty or not"?
I think one row, no matter how many rows
From what I see, it looks like SQLServer and PostgerSQL don't bother looking into table at all:
also returns just one row. Even though SQLServer docs says
that is not true in this case -
Oracle and Mysql behaviour seems more logical and correct to me...