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

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.

Clients receive the following error:

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.

For example, instead of the following query:

   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

Try the following query:

   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.