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 "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
Keywords: kbbug kbfix KB274470
