FIX: Regression Old Syntax Outer Join May Produce Error 613

ID: Q178443

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: NT 17126(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.

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 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.

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.

Additional query words:

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