FIX: Regression Old Syntax Outer Join May Produce Error 613 (178443)

SYMPTOMS

Using old style syntax, a query with an outer join gives good results, but on its next execution it causes an error 613, and all subsequent executions show the same behavior. This problem occurs if the following conditions are true:

The inner table has a minimum row length of 180 bytes.
The tables involved in the outer join have a minimum number of rows containing null values.
Table2 is the inner table.

SET SHOWPLAN ON, trace flag 302, and trace flag 310 do not give different results if the query runs correctly or fails with error 613. The following is the error message that is seen on the client, as well as on the SQL Server Errorlog:

Msg 613, Level 20, State 0

This problem occurs in SQL Server builds 6.50.240 and 6.50.258; the problem does not occur with SQL Server builds 6.50.201 and 6.50.213.

WORKAROUND

The script below shows the problem scenario and its two workarounds. Two different workarounds for this problem are provided after this script.

   set nocount on
   go
   drop table table1
   go
   create table table1 (cola int null)
   go
   drop table table2
   go
   CREATE TABLE table2 (
      col1 int NOT NULL,
      --The datatype is not important;
      --the NULL/NOT NULL property is important;
      --the row length is important.
      col2 char (176) NOT NULL
   )
   go

   declare @cpt int
   select @cpt = 1
   while (@cpt<=27)
   --Table1 needs at least 27 rows with a null value
   --in the column involved in the outer join.
   begin
      insert table1 select null
      select @cpt = @cpt+1
   end
   insert table1 select 1
   go

   declare @cpt int
   select @cpt = 1
   while (@cpt<=1961) --This is the minimum number of rows.
   begin
   insert table2 select  1, ''a''
   select @cpt = @cpt+1
   end

Workaround 1

Force a convert on the column type on for the inner table (in this case, table2), as in the following example:

   SELECT table1.col1, table2.cola
      FROM table2,
         table1
   WHERE  table1.cola *= convert(int,table2.col1)

Workaround 2

Use the ANSI style syntax, as in the following example:

   SELECT table1.col1, table2.cola
      FROM table2 RIGHT OUTER JOIN table1
   ON table1.cola = table2.col1

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.