Microsoft KB Archive/181986

= FIX: Access Violation on SELECT Statement with UNION ALL =

Article ID: 181986

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q181986



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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Additional query words: prodsql sp sp5

Keywords: kbbug kbfix KB181986

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.