BUG: Regression Old Syntax Outer Join May Produce Error 613

• Microsoft SQL Server, version 6.5

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.

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 version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

It appears that the minimum number of rows on each table depends on the length of each row in the table description. The problem scenario given above gives the minimum length and the minimum number of rows for each table.