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

MORE INFORMATION

 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