Found a bug - is it known? can others reproduce it? -- sql server programming

Steve Kass
4/22/2004 4:53:32 PM

Hugo,

Thanks for the repro. I'll report this and post feedback here when I
hear something.

Steve Kass
Drew University

[quoted text, click to view]

Hugo Kornelis
4/22/2004 10:30:22 PM

Hi,

Today, I encountered a bug in SQL Server. My database is quite complex
so it took me quite some time to trim off everything that is not
directly related. I am still left with a long repro script (down
below).

First: the offending query:

SELECT *
FROM View1 AS b1
INNER JOIN View1 AS b2
ON b2.Col = 'c'
AND b2.Key1 <> b1.Key1
WHERE (SELECT COUNT(*)
FROM View2 AS x1
INNER JOIN View2 AS x2
ON x2.Key1 = b2.Key1
AND x2.Key2 = x1.Key2
WHERE x1.Key1 = b1.Key1)
IN ((SELECT COUNT(*)
FROM View2
WHERE Key1 = b1.Key1),
(SELECT COUNT(*)
FROM View2
WHERE Key1 = b2.Key1))

Given the definitions for View1 and View2 (see full repro script
below), executing this query results in the following error:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite
(WrapperWrite()).

Interesting note #1: If I remove either of the two subselects from the
IN clause, the query runs fine (but doesn't give the desired results,
of course).

Interesting note #2: If I create two temporary tables with an exact
image of the view's contents (select * into temp1 from view1 and ditto
for temp2) and run this query against the temp tables instead of the
views, the query runs fine.

Interesting note #3: As long as view1 is empty, has exactly one row or
has more rows but none of them have Col = 'c', the query runs fine and
returns an empty result set. But as soon as the number of rows is 2 or
more and at least 1 has Col = 'c', the above error results.

I hope that someone in the newsgroup can tell me whether this is a
known issue. Also, I'd be interested to know if the others get the
same error from this script. If not, it must be something on my
system.

Note: output from select @@version is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack
4)

Full repro script:

CREATE TABLE Lookup (Number int NOT NULL,
Userid varbinary(85) NOT NULL,
PRIMARY KEY (Number),
UNIQUE (Userid)
)
go
insert Lookup (Number, Userid)
select 1, SUSER_SID()
go
CREATE TABLE Table1 (Key1 varchar(40) NOT NULL,
Col1 char(10) NULL,
Col2 char(10) NULL,
Code char(1) NULL,
Number int NULL,
PRIMARY KEY (Key1)
)
GO
CREATE VIEW View1 AS
SELECT Key1,
CASE
WHEN Lookup.Number = Table1.Number
THEN Col1
ELSE Col2
END AS Col
FROM Table1
LEFT JOIN Lookup
ON Lookup.Userid = SUSER_SID()
WHERE Table1.Number IS NULL
OR Table1.Code IS NULL
OR ( Table1.Number = Lookup.Number
AND Table1.Code = 'A')
OR ( Table1.Number != COALESCE(Lookup.Number, 0)
AND Table1.Code != 'A')
GO
insert Table1 (Key1, Col1, Col2, Code, Number)
select 'k1', 'x', null, 'A', 1
union all
select 'k2', 'c', null, 'A', 1
go
CREATE TABLE Table2 (Key1 varchar(40) NOT NULL,
Key2 char(10) NOT NULL,
Code char(1) NULL,
Number int NULL,
PRIMARY KEY (Key1, Key2),
FOREIGN KEY (Key1) REFERENCES Table1(Key1)
)
GO
CREATE VIEW View2 AS
SELECT Key1, Key2
FROM Table2
LEFT JOIN Lookup
ON Lookup.Userid = SUSER_SID()
WHERE Table2.Number IS NULL
OR Table2.Code IS NULL
OR ( Table2.Number = Lookup.Number
AND Table2.Code = 'A')
OR ( Table2.Number != COALESCE(Lookup.Number, 0)
AND Table2.Code != 'A')
GO
insert Table2 (Key1, Key2, Code, Number)
select 'k1', 'aa', 'A', 1
union all
select 'k2', 'aa', 'A', 1
union all
select 'k2', 'bb', 'A', 1
go
SELECT *
FROM View1 AS b1
INNER JOIN View1 AS b2
ON b2.Col = 'c'
AND b2.Key1 <> b1.Key1
WHERE (SELECT COUNT(*)
FROM View2 AS x1
INNER JOIN View2 AS x2
ON x2.Key1 = b2.Key1
AND x2.Key2 = x1.Key2
WHERE x1.Key1 = b1.Key1)
IN ((SELECT COUNT(*)
FROM View2
WHERE Key1 = b1.Key1),
(SELECT COUNT(*)
FROM View2
WHERE Key1 = b2.Key1))
go
drop view View2
go
drop table Table2
go
drop view View1
go
drop table Table1
go
drop table Lookup
go

Best, Hugo
--

Kostas
4/23/2004 4:41:35 AM

Hello Steve,

How come you have version 8.00.818, what are other
updates exist that I missed?

Thanks in advance.

[quoted text, click to view]

Steve Kass
4/23/2004 5:25:04 AM

Hugo,

I got the same error, running version 8.00.818, and another SQL Server
MVP verified the error on 8.00.760. Do you need a workaround? I
suspect there's a way to rewrite this sort of query to avoid the error,
so let me know if you need something. While this is almost certainly a
bug, a workaround might be preferable to waiting for a hotfix or service
pack that fixes the problem.

SK

[quoted text, click to view]

Hugo Kornelis
4/23/2004 11:01:43 AM

[quoted text, click to view]

Hi Steve,

Thanks - saves me the hassle.

BTW, did you get the same results?

Best, Hugo
--

Hugo Kornelis
4/23/2004 1:08:04 PM

[quoted text, click to view]

Hi Steve,

Thanks for confirming it's not something funny on my system. :-)

I've already found a workaround. Replaced

AND (subquery1) IN ((subquery2), (subquery3))

with

AND ( (subquery1) = (subquery2)
OR (subquery1) = (subquery3))

Probably a bit slower (double execution of subquery1), but it works.
I'm now back to finding and solving my own bugs.

Best, Hugo
--

Steve Kass
4/23/2004 1:39:19 PM

Kostas,

A helpful summary of version numbers is at
http://www.tacktech.com/display.cfm?ttid=60. In particular, the version
rises to 8.00.818 when the cumulative security patch MS03-31 has been
applied to SQL Server 2000, service pack 3.

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Found a bug - is it known? can others reproduce it?