FIX: SELECT DISTINCT May Return Incorrect Results Unless Column Set Equal to Itself

Article translations Article translations
Article ID: 274470 - View products that this article applies to.
This article was previously published under Q274470
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58253 (SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

The script shown in the "More Information" section creates some tables and indexes, and then populates the tables. It then performs two simple queries that select rows from the tables using simple equality statements in the WHERE clause. The two queries are identical except that the second query contains a statment in the WHERE clause that compares one column in one table to itself. That statement is not included in the first query, which incorrectly only returns 8 rows. The second query, with the extra statement in the WHERE clause, correctly returns 1015 rows.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

MORE INFORMATION

Here is the script that reproduces this problem:
 use tempdb
 go
 
 set nocount on
 go

 create table Subjects (
   SubjID   int not null,
   SubjName varchar(255) not null,
   constraint pkSubjects primary key clustered (SubjID)
 )
 go
 
 create table Documents (
   DocID int not null,
   CreID int not null,
   DebID int not null,
   constraint pkDocuments primary key clustered (DocID),
   constraint fkDocuments_CreID foreign key (CreID) references
 Subjects(SubjID),
   constraint fkDocuments_DebID foreign key (DebID) references
 Subjects(SubjID)
 )
 go
 
 create index inDocuments_CreID on Documents(CreID)
 go
 create index inDocuments_DebID on Documents(DebID)
 go
 
 
 create table Producers (
   ProdID int not null,
   constraint pkProducers primary key clustered (ProdID)
 )
 go
 
 create table Items (
   ItemID int not null,
   ProdID int not null,
   constraint pkItems primary key clustered (ItemID),
   constraint fkItems_ProdID foreign key (ProdID) references
 Producers(ProdID)
 )
 go
 
 create index inItems_ProdID on Items(ProdID)
 go
 
 create table Details (
   DocID    int not null,
   ItemID   int not null,
   constraint pkDetails primary key clustered (DocID, ItemID),
   constraint fkDetails_DocID foreign key (DocID) references
 Documents(DocID),
   constraint fkDetails_ItemID foreign key (ItemID) references 
 Items(ItemID)
 )
 go 

 declare @n int
 declare @k int
 declare @prods_count int
 declare @items_count int
 declare @docs_count int
 declare @subjs_count int
 declare @max_details int
 declare @max_cre_id int
 
 set @prods_count = 10
 set @items_count = 500
 set @subjs_count = 1000
 set @docs_count = 1000
 set @max_details = 20
 
 set @max_cre_id = 100
 
 set @n = 1
 while @n <= @prods_count
 begin
   insert into Producers values (@n)
   set @n = @n + 1
 end
 
 set @n = 1
 while @n <= @items_count
 begin
   insert into Items values (@n,
 1.0*@n*@n/(@items_count*@items_count)*(@prods_count-1) + 1)
   set @n = @n + 1
 end
 
 set @n = 1
 while @n <= @subjs_count
 begin
   insert into subjects values (@n, 'S_' + cast(@n as varchar))
   set @n = @n + 1
 end
 
 set @n = 1
 while @n <= @docs_count
 begin
   insert into Documents values (@n, (@n % @max_cre_id) + 1, (@n * @n) %
 @subjs_count + 1)
   set @n = @n + 1
 end
 declare @item_id int, @prev_id int
 
 set @n = 1
 while @n <= @docs_count
 begin
   set @prev_id = -1
   set @k = 1
 
   while @k <= (@n % @max_details) + 1
   begin
     set @item_id = 
 1.0*(@n+@k)/(@docs_count+@max_details)*(@items_count-1)+1
     if @item_id <> @prev_id
     begin
       insert into details values (@n, @item_id) --,1)
       set @prev_id = @item_id
     end
     set @k = @k + 1
   end
   set @n = @n + 1
 end
 go
 
 -- Query incorrectly returns only 8 rows on SQL 7

 set nocount off
 go 

 select distinct
   deb.SubjID as DebID,  deb.SubjName as DebName,
   cre.SubjID as CreID,  cre.SubjName as CreName,
   p.ProdID
 from
   Documents doc ,
   Details det,
   Subjects cre,
   Subjects deb,
   Items     i,
   Producers p
 where
   doc.DocID = det.DocID and
   doc.CreID = cre.SubjID and
   doc.DebID = deb.SubjID and
   det.ItemID = i.ItemID and
   i.ProdID = p.ProdID
 
 -- Query returns correct 1015 rows on SQL 7
 
 select distinct
   deb.SubjID as DebID,  deb.SubjName as DebName,
   cre.SubjID as CreID,  cre.SubjName as CreName,
   p.ProdID
 from
   Documents doc ,
   Details det,
   Subjects cre,
   Subjects deb,
   Items     i,
   Producers p
 where
   doc.DocID = det.DocID and
   doc.CreID = cre.SubjID and
   doc.DebID = deb.SubjID and
   det.ItemID = i.ItemID and
   i.ProdID = p.ProdID
   and p.ProdID = p.ProdID   -- !!! ProdID is the primary key of Producers
 go
				

Properties

Article ID: 274470 - Last Review: October 21, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB274470

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com