Adaptive Server version 12.5 does not process correlated subqueries containing Transact-SQL outer joins in the same way that earlier versions of Adaptive Server did. The following is an example of a query using a correlated variable as the outer member of a Transact-SQL outer join:
select t2.b1, (select t2.b2 from t1 where t2.b1 *= t1.a1) from t2
Earlier versions of Adaptive Server used trace flag 298 to display error messages for these queries. Depending on whether trace flag 298 was turned on or off and whether the query used the correlated variable as an inner or outer member of an outer join, Adaptive Server displayed the behavior described in Table 4-1:
Type of query |
Trace flag 298 turned off |
Trace flag 298 turned on |
|---|---|---|
Correlated as an inner member of an outer join |
Disallowed: produces error message 11013 |
No error |
Correlated as an outer member of an outer join |
No error |
Disallowed: produces error message 301 |
Adaptive Server reverses the behavior of trace flag 298. Because Adaptive Server version 12.5 translates Transact-SQL outer joins into ANSI outer joins during the preprocessor stage, there is the potential for different results when allowing such queries to run. Allowing correlated subqueries that contain Transact-SQL outer joins to run with the 298 trace flag turned on is consistent with Sybase’s historical trace flag usage. For version 12.5, the behavior of trace flag 298 is:
Type of query |
Trace flag 298 turned off |
Trace flag 298 turned on |
|---|---|---|
Correlated as an inner member of an outer join |
Disallowed: produces error message 11013 |
Disallowed: produces error message 11013 |
Correlated as an outer member of an outer join |
Disallowed: produces error message 11055 |
No error |
Adaptive Server has changed error message 301 to error message 11055, although the text of the message remains the same.
