FIX: SELECT INTO w/ Correlated Subqueries May Cause AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

In Microsoft SQL Server version 6.0, a SELECT INTO statement that includes a correlated subquery using the aggregate functions SUM or AVG may cause a client access violation (AV).

CAUSE

The specific conditions under which this problem occurs are:

• SELECT INTO statement /*the SELECT works fine without INTO clause*/
• Correlated subquery in the <select_list>
• SUM or AVG in the correlated subquery /* the statement works fine with MIN and MAX */

WORKAROUND

The correlated subquery works when MAX or MIN is the aggregate function. However, when the AVG or SUM functions are used, then you should not use the correlated query, but split the query and use GROUP BY in the first query, get those results, and join with the tables in the second query to achieve the same results as in the correlated subquery.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

Example Scenario

The following script reproduces the client AV:

   use master
   go
   sp_dboption pubs,'select',true
   go
   use pubs
   go
   checkpoint
   go
   create table t11
   ( col1 int,
     col2 int )
   go
   create table t22
   (col1 int )
   go
   insert into t11 values (1,2)
   go
   insert into t11 values (3,4)
   go
   insert into t11 values (2,5)
   go
   insert into t11 values (1,5)
   go
   insert into t22 values (1)
   go
   insert into t22 values (2)
   go
   /* case 1: using a temporary table in INTO clause */
   select t22.col1,(select sum(col2) from t11 where col1 = t22.col1)
   into #temp_table
   from t22
   go
   /* case 2: using a table in user database in the  INTO clause */
   select t22.col1,(select sum(col2) from t11 where col1 = t22.col1)
   into userdb_table
   from t22
   go
   /* case 3: using AVG in subquery */
   select t22.col1,(select avg(col2) from t11 where col1 = t22.col1)
   into #temp_table
   from t22
   go