The following V8.0.2 Help topic describes a behavior change with an ominous phrase "result sets may change" but no examples of that case, only the (much less ominous) "may produce error messages".
Please provide an example of the "result sets may change" case... it's kind of important when upgrading from V5 to V11 :)
Adaptive Server Anywhere SQL User's Guide 7. Summarizing, Grouping and Sorting Query Results Summarizing query results using aggregate functions Where you can use aggregate functions
. . .
Aggregate functions and outer references
Adaptive Server Anywhere version 8 follows new SQL/99 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 correct 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 now treated as an outer reference. This means that the aggregate function is now computed in the outer block, not in the subquery, and becomes a constant within the subquery.
The following restrictions now apply to the use of outer reference aggregate functions in subqueries:
Note that 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.
The following query produced the following results in Adaptive Server Anywhere version 7.
SELECT name, (SELECT SUM(p.quantity) FROM sales_order_items) FROM product p name sum(p.quantity) Tee shirt 30,716 Tee shirt 59,238
In version 8, the same query produces the error message ASA Error -149: Function or column reference to 'name' must also appear in a GROUP BY. The reason that the statement is no longer valid is that the outer reference aggregate function sum(p.quantity) is now computed in the outer block. In version 8, the query is semantically equivalent to the following (except that Z does not appear as part of the result set):
SELECT name, SUM(p.quantity) as Z, (SELECT Z FROM sales_order_items) FROM product p
Since the outer block now computes an aggregate function, the outer block is treated as a grouped query and column name must appear in a GROUP BY clause in order to appear in the SELECT list.
Thanks for pointing out this issue. The documentation will be changed to include an example for the case when "result sets may change":
SELECT (SELECT first SUM(p.quantity) FROM sales_order_items) ss FROM product p where id = 300
In SA 7, the result is: 30,716
answered 19 Jul '12, 17:45