BUG: AV Querying a View w/ CASE WHEN EXISTS and FORCEPLAN Is ON

• Microsoft SQL Server, version 6.5

SYMPTOMS

When querying a view with a CASE WHEN EXISTS clause and FORCEPLAN is ON, an access violation (AV) may occur. The following sample scripts demonstrate this problem:

   DROP TABLE t
   GO

   SELECT c = 1
      INTO t
   UNION
   SELECT c = 2

   DROP VIEW vT
   GO

   CREATE VIEW vT
   AS
      SELECT CASE WHEN EXISTS
         (SELECT * FROM t t1 WHERE t1.c = t2.c)
      THEN 1
      ELSE 0
      END AS col
      FROM t t2
   GO

   SET FORCEPLAN ON
   GO

   SELECT * FROM vT
   GO

   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
   0x0051B4DE in SQLSERVR.EXE, find_eop_subst() + 0x00CE
   0x004B3493 in SQLSERVR.EXE, MSSqlSDINewSP() + 0x0173
   0x0051A905 in SQLSERVR.EXE, prEOP() + 0x0165
   0x004B2D9C in SQLSERVR.EXE, MSSqlSDIGetVars() + 0x023C
   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
   0x00250FED in opends60.dll
   0x0025055B in opends60.dll
   0x002414D1 in opends60.dll
   0x00241384 in opends60.dll
   0x10219D84 in MSVCRT40.dll
   0x77F04F32 in KERNEL32.dll
   ***END STACK TRACE***

   DB-Library Process Dead - Connection Broken

WORKAROUND

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

• Issue a SET FORCEPLAN OFF command. Doing this avoids the problem.

  -or-

• Create a temporary table instead of using a view. The following scripts demonstrate the workaround for the above scenario:

        DROP TABLE t
        GO
  

        SELECT c = 1
           INTO t
        UNION
        SELECT c = 2
  
        SELECT CASE WHEN EXISTS
           (SELECT * FROM t t1 WHERE t1.c = t2.c)
        THEN 1
        ELSE 0
        END AS col
        INTO #t
        FROM t t2
  
        SET FORCEPLAN ON
        GO
  
        SELECT * FROM #T
        GO
  
  

STATUS

Microsoft has confirmed this to be a problem in Microsoft 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.