sql - HAVING without GROUP BY

Question

1. Is the following possible according to standard(!) SQL?
2. What minimal changes should be neccessary in order to be conforming to the standard (if it wasn't already)?
3. It works as expected in MySQL, iff the first row has the maximum value for NumberOfPages.

SELECT * FROM Book HAVING NumberOfPages = MAX(NumberOfPages)

The following is written in the standard:

HAVING <search condition>

• Let G be the set consisting of every column referenced by a <column reference> contained in the <group by clause>.
• Each column reference directly contained in the <search condition> shall be one of the following:
  1. An unambiguous reference to a column that is functionally dependent on G.
  2. An outer reference.

source

Can somebody explain me, why it should be possible according to the standard?

In MySQL, it perfectly works.

Answer 1

Despite the Mimer Validator result, I don't believe yours is valid Standard SQL.

A HAVING clause without a GROUP BY clause is valid and (arguably) useful syntax in Standard SQL. Because it operates on the table expression all-at-once as a set, so to speak, it only really makes sense to use aggregate functions. In your example:

Book HAVING NumberOfPages = MAX(NumberOfPages)

is not valid because when considering the whole table, which row does NumberOfPages refer to? Likewise, it only makes sense to use literal values in the SELECT clause.

Consider this example, which is valid Standard SQL:

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

Despite the absence of the DISTINCT keyword, the query will never return more than one row. If the HAVING clause is satisfied then the result will be a single row with a single column containing the value 'T' (indicating we have books with differing numbers of pages), otherwise the result will be the empty set i.e. zero rows with a single column.

I think the reason why the query does not error in mySQL is due to propritary extensions that cause the HAVING clause to (logically) come into existence after the SELECT clause (the Standard behaviour is the other way around), coupled with the implicit GROUP BY clause mentioned in other answers.