Troubleshooting: Aggregate functions and outer references

SQL Anywhere follows SQL/2008 standards for clarifying the use of aggregate functions when they appear in a subquery. These changes affect the behavior of statements written for previous versions of the software: previously valid queries may now produce error messages and result sets may change.

When an aggregate function appears in a subquery, and the column referenced by the aggregate function is an outer reference, the entire aggregate function itself is treated as an outer reference. The aggregate function is computed in the outer query block, not in the subquery, and becomes a constant within the subquery.

The following restrictions apply to the use of outer reference aggregate functions in subqueries:

The outer reference aggregate function can only appear in subqueries that are in the SELECT list or HAVING clause, and these clauses must be in the immediate outer block.
Outer reference aggregate functions can only contain one outer column reference.
Local column references and outer column references cannot be mixed in the same aggregate function.

Some problems related to the new standards can be circumvented by rewriting the aggregate function so that it only includes local references. For example, the subquery (SELECT MAX(S.y + R.y) FROM S) contains both a local column reference (S.y) and an outer column reference (R.y), which is now illegal. It can be rewritten as (SELECT MAX(S.y) + R.y FROM S). In the rewrite, the aggregate function has only a local column reference. The same sort of rewrite can be used when an outer reference aggregate function appears in clauses other than SELECT or HAVING.

Example 1

The following query produced valid results in SQL Anywhere 7 and earlier versions:

SELECT Name, 
      ( SELECT SUM( p.Quantity )
       FROM SalesOrderItems )
FROM Products p WHERE p.ID = 300;

Name	SUM(p.Quantity)
Tee shirt	30,716

In SQL Anywhere 8 and later versions, the same query produces an error message stating that the function or column reference must appear in a GROUP BY clause. The statement is no longer valid because SUM(p.Quantity) is treated as an outer reference and computed in the outer query block. The above query is equivalent to the following query:

SELECT Name,
       SUM( p.Quantity ) AS Z,
       ( SELECT Z 
            FROM SalesOrderItems )
   FROM Products p WHERE p.ID = 300;

However, since the aggregate function is now computed in the outer query block, the outer query block is treated as a grouped query and the column name must appear in a GROUP BY clause in the SELECT list. Therefore, this query is also invalid and produces the same error message. To return the same result set in SQL Anywhere 8 and later versions as the first query did in SQL Anywhere 7 and previous versions, you must use the following query:

SELECT Name,
p.Quantity * ( SELECT COUNT( * ) FROM SalesOrderItems )
FROM Products p WHERE p.ID = 300;

Example 2

In SQL Anywhere 7 and earlier versions, the following query produced the result 30,716, because SUM(p.Quantity) was computed inside the nested SELECT query block and only p.Quantity was treated as an outer reference.

SELECT ( SELECT FIRST SUM( p.Quantity ) FROM SalesOrderItems ) AS ss 
FROM Products p WHERE p.ID = 300;

In SQL Anywhere 8 and later versions, the same query returns the result 28, because SUM(p.Quantity) is treated as an outer reference and is computed in the outer query block. In other words, the above query is equivalent to the following query:

SELECT DT.ss
FROM ( SELECT SUM( p.Quantity ) AS asum,
             ( SELECT FIRST asum FROM SalesOrderItems ) AS ss
      FROM Products p WHERE p.ID = 300 ) AS DT;

To return the same results in SQL Anywhere 8 and later versions as the first query did in SQL Anywhere 7 and previous versions, you must use the following query:

SELECT p.Quantity * ( SELECT COUNT( * ) FROM SalesOrderItems ) AS ss
FROM Products p WHERE p.ID = 300;