BUG: AV in Query w/Subselect on View w/JOIN and FORCEPLAN Is On

• Microsoft SQL Server version 6.5

SYMPTOMS

A handled access violation (AV) may occur if all of the following conditions are true:

• A query references a view.
• The view contains a join.
• The query contains a subselect.
• SET FORCEPLAN ON was run before the query.

   DB-Library Process Dead - Connection Broken

   [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream

   EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump
   Initializing symptom dump and stack dump facilities
   ***BEGIN STACK TRACE***
   0x0042F2C8 in sqlservr.EXE, newlinklock() + 0x0418
   0x00433717 in sqlservr.EXE, opendb() + 0x00F7
   0x0042ED45 in sqlservr.EXE, check_deadlock() + 0x0675
   0x0042EFA8 in sqlservr.EXE, newlinklock() + 0x00F8
   0x00423B50 in sqlservr.EXE, dbswriteflush() + 0x0160
   0x004229BD in sqlservr.EXE, dbswritecheck() + 0x07AD
   0x0040E680 in sqlservr.EXE, ksconsole() + 0x0320
   0x0040F1E5 in sqlservr.EXE, initcfgfix() + 0x03D5
   0x0040ED45 in sqlservr.EXE, initconfig() + 0x0165
   0x0040B7B2 in sqlservr.EXE, SqlDumpLocks() + 0x0052
   0x00415217 in sqlservr.EXE, udasyncwrite() + 0x0187

WORKAROUND

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

• Do not use SET FORCEPLAN ON for the query.

  -or-

• Change the query to avoid one of the other causes. For the most part, you can do this by using a join instead of a subselect.

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.

MORE INFORMATION

Steps to Reproduce the Problem

1. Create the view:

         use pubs
         go
         create view v1 AS
         select ta.title_id, t.pub_id
         from titleauthor ta
         inner join titles t on t.title_id = ta.title_id
   

2. Use the following query to cause an AV:

         set forceplan on
         select distinct * from v1
         where pub_id in (select pub_id from publishers)
         set forceplan off
   

   set forceplan on
   select distinct v1.* from v1
   inner join publishers p on v1.pub_id=p.pub_id
   set forceplan off