FIX: Access Violation on SELECT Statement with UNION ALL

ID: Q181986

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: NT 17572 (6.5)

SYMPTOMS

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

A SELECT statement has two sub-selects that are UNIONed together

The second sub-select statement contains a sub-select that is a JOIN.

Although the following scenario works correctly under SQL Server 6.00.121, it demonstrates the problem under all builds of SQL Server 6.5:


create table t1 (c1 numeric(10), c2 varchar(10), c3 numeric(10))
   go
   create table t2 (c1 numeric(10))
   go
   insert into t1 values (1, 'temp', 1)
   go
   insert into t2 values (1)
   go
   SELECT c1
   FROM t1
   WHERE c1 in
   ((select c1 from t1 where c2 like 'value%')
   union
   (select c1 from t1 where c1 not in
       (select t1.c1 from t1, t2
            where t1.c3 = t2.c1 and
            t1.c2='value')
   ))

The following debug stack trace is generated by all builds of SQL Server 6.5, up to and including build 281:


   EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump
   Initializing symptom dump and stack dump facilities
   ***BEGIN STACK TRACE***
   0x004BE1BC in debugsr.EXE, merge_subqjoin() + 0x000E
   0x004BC87F in debugsr.EXE, build_subqlist() + 0x0150
   0x004BCA7F in debugsr.EXE, build_subqlist() + 0x0350
   0x004BC4DB in debugsr.EXE, build_subqjoin() + 0x057A
   0x004BE82B in debugsr.EXE, pull_subq() + 0x00FA
   0x004BC337 in debugsr.EXE, build_subqjoin() + 0x03D6
   0x004BE82B in debugsr.EXE, pull_subq() + 0x00FA
   0x004BBC1B in debugsr.EXE, build_derived() + 0x03AF
   0x004BBF0F in debugsr.EXE, build_union() + 0x002A
   0x004BBE6D in debugsr.EXE, build_derived() + 0x0601
   0x004BEC93 in debugsr.EXE, pull_derived() + 0x00C9
   0x004BC2D5 in debugsr.EXE, build_subqjoin() + 0x0374
   0x004BE82B in debugsr.EXE, pull_subq() + 0x00FA
   0x004C8D4A in debugsr.EXE, join() + 0x00AA
   0x006609AD in debugsr.EXE, decision() + 0x09ED
   0x0065E756 in debugsr.EXE, build_plan() + 0x09D6
   0x0054482A in debugsr.EXE, s_compilestep() + 0x0179
   0x00543D5F in debugsr.EXE, s_compile() + 0x0331
   0x00507B4C in debugsr.EXE, sequencer() + 0x025C
   0x00415E53 in debugsr.EXE, language_exec() + 0x0698
   0x0024176B in opends60.dll
   0x002414B4 in opends60.dll
   0x00243AC3 in opends60.dll
   0x002428EA in opends60.dll
   0x10219D84 in MSVCRT40.dll
   0x77F04F4A in KERNEL32.dll
   ***END STACK TRACE***

The following retail-build stack trace is generated on SQL Server 6.5 build 281:


   ***BEGIN STACK TRACE***
   0x004FB1BC in SQLSERVR.EXE, cnst_drop() + 0x05DC
   0x0048E80C in SQLSERVR.EXE, mny_round() + 0x00AC
   0x0042534C in SQLSERVR.EXE, fprhdrs() + 0x01DC
   0x004FA137 in SQLSERVR.EXE, cnst_crref() + 0x0A77
   0x0048E932 in SQLSERVR.EXE, signfn() + 0x00C2
   0x004F9E2B in SQLSERVR.EXE, cnst_crref() + 0x076B
   0x0048E932 in SQLSERVR.EXE, signfn() + 0x00C2
   0x004F98B0 in SQLSERVR.EXE, cnst_crref() + 0x01F0
   0x004F9B7E in SQLSERVR.EXE, cnst_crref() + 0x04BE
   0x004F9B47 in SQLSERVR.EXE, cnst_crref() + 0x0487
   0x0048E98C in SQLSERVR.EXE, signfn() + 0x011C
   0x004F9DE9 in SQLSERVR.EXE, cnst_crref() + 0x0729
   0x0048E932 in SQLSERVR.EXE, signfn() + 0x00C2
   0x00423A3F in SQLSERVR.EXE, dbswriteflush() + 0x003F
   0x00421A4C in SQLSERVR.EXE, nmpsgetinfo() + 0x00DC
   0x00422A78 in SQLSERVR.EXE, dbswritecheck() + 0x0858
   0x004222E9 in SQLSERVR.EXE, dbswritecheck() + 0x00C9
   0x004225BC in SQLSERVR.EXE, dbswritecheck() + 0x039C
   0x004097E8 in SQLSERVR.EXE, opencheck() + 0x0048
   0x00427B09 in SQLSERVR.EXE, tbswritecheck() + 0x0969
   0x00250FED in opends60.dll
   0x0025055B in opends60.dll
   0x002414D1 in opends60.dll
   0x00241384 in opends60.dll
   0x10219D84 in MSVCRT40.dll
   0x77F04F2C in KERNEL32.dll
   ***END STACK TRACE***

WORKAROUND

To work around this problem, rewrite the query to put the sub-sets into a temporary table and then UNION the contents of them together, as in the following example:


select c1 into #t1 from t1 where c2 like 'value%'
   go
   select c1 into #t2 from t1 where c1 not in(select t1.c1 from t1, t2
   where t1.c3 = t2.c1 and t1.c2='value')
   go
   SELECT c1
   FROM t1
   WHERE c1 in
   (
   select * from #t1
   union
   select * from #t2
   )

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: prodsql sp sp5


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

Last Reviewed: April 21, 1999