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

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)
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: 10/21/2013 01:50:09 - Revision: 2.1

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB274470
Feedback