The Curious Case of the Aggregation Query

An interesting query made the rounds recently. The related blog post by Justin Jaffray compares four queries, one of them catching everybody’s attention. I invite you to have a look for yourself. First, the table definitions:

CREATE TABLE aa (a INT);
INSERT INTO aa VALUES (1), (2), (3);
CREATE TABLE xx (x INT);
INSERT INTO xx VALUES (10), (20), (30);

There is not trap in there: two tables containing three rows each. Just note that column a belongs to the table aa and x to xx. Now the mysterious query:

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

What does it return?

Even if you look carefully, chances are that you’ll get it wrong—just like I did.

Later in the article we will see that SQL implementations don’t even agree about the correct result. Some return the values 3, 6 and 9 in a table of three rows. While this makes sense, it is not what the SQL standard says.

The standard-conforming result of the query is a single row with the value 6, and this is what most tested systems return. Surprised? You are not alone. To clarify how it works I will provide some quotes from the current SQL standard (SQL:2023) as well as older releases to help us track down the origin of this behavior.

The bottom line is that aggregate functions (actually set function specifications) do not necessarily belong to the select in which are they written. In the example above, the expression sum(a) does not belong to the inner query, but to the outer one. The query actually means the following:

SELECT (SELECT sum_a
          FROM xx
         LIMIT 1
       )
  FROM (SELECT sum(a) AS sum_a
          FROM aa
       ) nested

The subquery in the from clause does the aggregation first. The select clause subquery just refers to the other query’s result. Now it is also clear why there is a limit clause: Without this clause, the subquery returns all three rows from xx so that the query fails—conforming systems do exactly that.

The striking question is of course: What the heck is going on here? This question is actually meant to be two questions in one: (1) What is the wording in the standard that leads to this behavior? (2) Why was it ever specified in this way?

The first question can be answered by looking into the current standard. Specifically, into ISO/IEC 9075-2:2023 §6.9 SR 6:

The aggregation query of a <set function specification> SFS is determined as follows.

Case:

1. If SFS has no aggregated column reference […]

2. Otherwise, the innermost qualifying query of the aggregated column references of SFS is the aggregation query of SFS.

The first sentence introduces the term aggregation query. This term is then used to specify which rows an aggregate function processes—and also where to add group by () if needed.0 Case “a” does not apply to our example as there is an aggregated column reference in the sum function, namely a. Case “b” is what we’re here for: it defines the innermost qualifying query as the aggregation query. Qualifying queries are, in turn, those in which the from/join clause introduces a table that is referred to by an argument of the aggregate function.1 Let’s have a look at the original query again.

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

The only column reference in the sum function refers to a column of the table aa introduced by the outer from clause. Thus, the sum(a) expression belongs to the outer select. Now that the outer query is an aggregation query that has no explicit group by clause, group by () is effectively added so that the final result has a single row.

On the other hand, there is no aggregate function referring to the inner query. This means that the inner query is not an aggregation query and there is no group by () implied! The inner query, which is supposed to return no more than one row, basically returns all three rows of the xx table. It is just the limit clause preventing it.2

Now, that we know how the standard specifies this behavior, let’s look which products follow the specification.

1. Aggregation query is always the one that contains the set function
2. Outer reference must be the only argument (doesn’t support F441)

Only half of the tested products implement the aggregation query in a standard-conforming way. That makes me wonder if the standard should introduce an implementation defined item here. However, in the chart it is also notable that some systems changed their behavior in the past. I checked older releases of the standard and found that the current wording was apparently introduced in 2003. This is at least what MySQL bug 27333 suggests. The bug report complains about MySQL’s behavior and refers to “the SQL2003 draft: (section 6.9)”. The report also paraphrases the above-quoted specification of the aggregation query.

SQL:1999 was worded entirely differently in this regard. The term aggregation query did not exist. I couldn’t even find any other sections that would result in the behavior of the current standard. Going even further back in time, to the well-known draft of SQL-92 that can easily be found online, there was yet another wording. The absence of an explicit group by clause did not imply group by () if needed. Instead, there was a general rule—not a syntax rule—that took care of doing the implicit grouping if necessary. ISO/IEC 9075:1992 §7.9 GR 1ai says:

If the <select list> contains a <set function specification> that contains a reference to a column of T [or …], then [… and] the result of the <query specification> is a table consisting of 1 row.

Emphasis and omissions are mine. Note that the outer select still contains the sum function because the term contains is defined recursively.3 For your convenience, the query again.

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

As the outer select list contains a <set function specification>, namely sum, the result is a single row. So it seems that this behavior has been there since 1992 at least. As I don’t have access to any even older editions, my archaeological excursion ends here.

Oh, just one more thing…why is it that SQL Server is so picky about outer references in aggregates? Changing the query to sum(a + x) yields error message 8124: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

After further review of the current standard, it turns out it has this limitation. However, implementing the optional feature F441 lifts it. Quoting ISO/IEC 9075-2:2023 §10.9 CR 8:

Without Feature F441, “Extended set function support”, conforming SQL language shall not contain a <value expression> simply contained in a <general set function> that contains a column reference that is an outer reference where the <value expression> is not a column reference.

The standards wording is even more restricting than SQL Server’s error message: in the presence of an outer reference, it rules out everything else—not just other column references. Out of curiosity I checked SQL:1992 again and found something interesting in ISO/IEC 9075:1992 §6.5 SR 4:

If the <value expression> contains a <column reference> that is an outer reference, then that outer reference shall be the only <column reference> contained in the <value expression>.

In SQL:1992, there was precisely the same limitation as expressed by SQL Server’s current error message.

Now, that the relation of SQL Server’s restriction to the standard is also clarified, there remains only one ultimate question: Why the heck was this behavior ever introduced?