Microsoft KB Archive/274470

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

Article ID: 274470

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q274470



BUG #: 58253 (SQLBUG_70)



SYMPTOMS
The script shown in the &quot;More Information&quot; 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

Keywords: kbbug kbfix KB274470

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.