FIX: Access Violation When Querying a View with a CASE WHEN EXISTS Clause 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 one of the following:

• Issue a SET FORCEPLAN OFF command. Doing this avoids the problem.
• 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 SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: enabled disable

Keywords          : kbusage SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 24, 1999