BUG: AV If Update on a Column with CASE and Aggregate Functions

• Microsoft SQL Server, version 6.5

SYMPTOMS

Performing an update on a column leads to an access violation if both of the following conditions are true:

• The column is updated with a CASE expression that includes aggregate functions.

  -and-

• The first WHEN clause is not chosen by the CASE expression.

   This command did not return data, and it did not return any rows
   DB-Library Process Dead - Connection Broken

WORKAROUND

To work around this problem, do either of the following:

• Place the aggregate function(s) outside the CASE expression.

  -or-

• Remove the WHERE clause.

   update titles
   set titles.royalty = (select (case 1
               when 0 then avg(royalty)
               when 1 then avg(royalty/2)
               else avg(royalty/3)
               end)
            from roysched r
            where r.title_id = t.title_id)
   from titles t

   update titles
   set titles.royalty = (select  avg(case 1
               when 0 then royalty
               when 1 then royalty/2
               else royalty/3
               end)
            from roysched r
            where r.title_id = t.title_id)
   from titles t

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.