Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END

DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestIdint'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFF
I have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>
|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>
|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I had
SQL Server 2000 SP4. Only now did I read it a bit better to see we're running
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter.
Regards,
Wouter