November 7, 2007 at 8:10 pm
I just noticed this in a query - I tried to simplify it as much as I can and still produce the issue. The problem is that queries return inaccurate results - ones that do not satisfy the where criteria. Simple changes (such as dropping an index, or changing order of joins) fix the problem. The problem does not exist at all in SQL server 2005. The 2000 instance has the latest SP (8.00.2040). I'm not sure if this is a known bug - but it has caused serious issues in our production environment.
Here's sql to create the issue on a clean database:
use master
go
create database alitest
go
use alitest
go
CREATE TABLE [dbo].
([id] [int],
[Name] [nvarchar](63),
CONSTRAINT PK_S PRIMARY KEY CLUSTERED ([id]))
CREATE TABLE [dbo].[SI](
[id] [int],
[pid] [int],
[stid] [int],
CONSTRAINT PK_SI PRIMARY KEY CLUSTERED ([id]))
CREATE TABLE [dbo].[AL](
[id] [int],
[pid] [int],
[aid] [int],
[type] [nvarchar](63),
CONSTRAINT PK_AL PRIMARY KEY CLUSTERED ([id]))
CREATE TABLE [dbo].[A](
[id] [int],
[name] [nvarchar](63),
[nvarchar](63) NULL,CONSTRAINT PK_A PRIMARY KEY CLUSTERED ([id]))
CREATE TABLE [dbo].[C](
[id] [int],
[Name] [nvarchar](63)
) ON [PRIMARY]
CREATE TABLE [dbo].[SS](
[id] [int],
[stid] [int],
[sid] [int],
CONSTRAINT PK_SS PRIMARY KEY CLUSTERED ([id]))
--
CREATE INDEX SI_IX1 ON SI(stid, pid)
CREATE INDEX SS_IX1 ON SS(stid)
--
INSERT A(id,name,code) VALUES(1,'',NULL)
INSERT AL(id,aid,pid,type) VALUES(1,1,1,'')
INSERT S(id,name) VALUES(1,'a')
INSERT SI(id,pid,stid) VALUES(1,1,1)
INSERT SS(id,stid,sid) VALUES(1,1,1)
INSERT SS(id,stid,sid) VALUES(2,2,1)
go
-- Query (1) returns 1 row (expected 0 rows, correct in sql 2005)
SELECT SI.stid
FROM SI
LEFT JOIN (
Select AL.pid,
A.name AS AName
FROM AL
INNER JOIN A ON AL.aid = A.id
LEFT JOIN C ON a.code = C.Name
WHERE
AL.type = ''
)SubQueryON SI.pid = SubQuery.pid
INNER JOIN SS ON SS.stid = SI.stid
INNER JOIN SON SS.sid = S.id
WHERE SubQuery.AName is null
AND SI.stid =1
AND S.Name = 'a'
-- Query (1a), same as (1) but changed order of joins, returns 0 rows (expected 0 rows)
SELECT SI.stid
FROM SI
INNER JOIN SS ON SS.stid = SI.stid
INNER JOIN SON SS.sid = S.id
LEFT JOIN (
Select AL.pid,
A.name AS AName
FROM AL
INNER JOIN A ON AL.aid = A.id
LEFT JOIN C ON a.code = C.Name
WHERE
AL.type = ''
)SubQueryON SI.pid = SubQuery.pid
WHERE SubQuery.AName is null
AND SI.stid =1
AND S.Name = 'a'
-- (2) Same query as (1), but selecting different field, returns 0 rows (expected 0)
SELECT SubQuery.pid
FROM SI
LEFT JOIN (
Select AL.pid,
A.name AS AName
FROM AL
INNER JOIN A ON AL.aid = A.id
LEFT JOIN C ON a.code = C.Name
WHERE
AL.type = ''
)SubQueryON SI.pid = SubQuery.pid
INNER JOIN SS ON SS.stid = SI.stid
INNER JOIN SON SS.sid = S.id
WHERE SubQuery.AName is null
AND SI.stid =1
AND S.Name = 'a'
-- Same query as (2), but in a where exists, returns "1" (expected 0 rows, correct in sql 2005)
SELECT 1 WHERE EXISTS(
SELECT SubQuery.pid
FROM SI
LEFT JOIN (
Select AL.pid,
A.name AS AName
FROM AL
INNER JOIN A ON AL.aid = A.id
LEFT JOIN C ON a.code = C.Name
WHERE
AL.type = ''
)SubQueryON SI.pid = SubQuery.pid
INNER JOIN SS ON SS.stid = SI.stid
INNER JOIN SON SS.sid = S.id
WHERE SubQuery.AName is null
AND SI.stid =1
AND S.Name = 'a'
)
use master
go
drop database alitest
November 8, 2007 at 7:31 am
Ali,
I did a quick test
First on SQL 2000.2187 (incl. cumultaive Hotfixes), all queries return the expected result.
Second on SQL 2000.2039 the results are like you said.
Then I rembered an issue I once ran into with a view and it urns out that thisis the case. Just change the order of the tables and put the derived table Subquery first, then it works as expected even on the older version.
Here's the example for the first query:
SELECT SI.stid
FROM (
Select AL.pid,
A.name AS AName
FROM AL
INNER JOIN A ON AL.aid = A.id
LEFT JOIN C ON a.code = C.Name
WHERE
AL.type = ''
)SubQuery LEFT JOIN SI ON SI.pid = SubQuery.pid
INNER JOIN SS ON SS.stid = SI.stid
INNER JOIN S ON SS.sid = S.id
WHERE SubQuery.AName is null
AND SI.stid =1
AND S.Name = 'a'
Markus
[font="Verdana"]Markus Bohse[/font]
November 8, 2007 at 7:35 am
Markus,
Very interesting. What was the issue with the view? Was this hotfix'ed?
November 8, 2007 at 7:41 am
Does anyone know which specific hotfix fixed this issue?
November 8, 2007 at 7:51 am
By the way - the same issue occurs if a view is used instead of the subquery.
November 8, 2007 at 7:59 am
Steve Jones - Editor (11/8/2007)
Markus,Very interesting. What was the issue with the view? Was this hotfix'ed?
Steve,
the issue with the view was exactly the same, that unless the derived table was the first table after the FROM it would return a wrong result.
Sorry but I don't know exactly the number of the Fix.
I ran into it about 5 years ago and I think it was fixed in SP4, but after running the query Ali provided, maybe it was even in one of the Hotfixes after that.
Markus
[font="Verdana"]Markus Bohse[/font]
November 8, 2007 at 8:02 am
Ali Soylu (11/8/2007)
Does anyone know which specific hotfix fixed this issue?
As I said before I don't know exactly which fix but you should get the cummulative package SQL2000-KB916287-v8.00.2187-x86x64-ENU.exe
[font="Verdana"]Markus Bohse[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply