SQL 2000 dies on JOIN query

[Martini]

Hello all.

I have quite disturbing situation where I am not happy about the way how SQL
handles the query. Situation is related to using user function in INNER JOIN
select. Although the problem occured in more complex situation the query can
be simplified to following example with same results:

There is an user function, that can be as simple as:

FUNCTION IsItSo (@text1 nvarchar(255), @text2 nvarchar(255))
RETURNS integer

BEGIN

if isnull(@text1,'')=isnull(@text2,'')
return 1
else
return 0

return 0
END

It can be any function returning integer, this is just for example.
Then there is a query as simple as that:
SELECT person, formula FROM
(
SELECT
a.person, v.code AS formula
FROM rows1 AS a
INNER JOIN rows2 AS v ON a.type=v.type
AND dbo.IsItSo(a.type,v.type)=1
)
as formulas
tables rows1 and rows2 can contain as little as two columns for this
example - rows1 have [person] and [type], rows2 [code] and [type]. All
columns are of nvarchar type.

So the AND clause after INNER JOIN is an obvious mistake. That is not the
problem, problem is how SQL reacts. On SQL 2000 SP4 the result for running
the query is following error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 101. Database may not be activated yet or may be
in transition.

On SQL 2000 SP3 the result is even more dramatic - SQL service will consume
100% of all available processors and become unresponsive. Only solution is
to restart either SQL service or the server.


My question is if this self-destructive behavior of SQL server can be
prevented by some configuration parameters or patches. I am a bit annoyed by
the fact that developer can kill the server by little bit of poor
programming that is syntactically acceptable for server.
One more curious bit - the section of the query located between parenthesis
can be run separately without any ill effects and get actual meaningful
results:
SELECT
a.person, v.code AS formula
FROM rows1 AS a
INNER JOIN rows2 v ON a.type=v.type
AND dbo.IsItSo(a.type,v.type)=1

[Alexander Kuznetsov]

Martini,

why do you want to do things like this? UDFs in WHERE clause are
usually performance killers. Isn't it simpler and faster to do just as
follows:

SELECT
a.person, v.code AS formula
FROM rows1 AS a
INNER JOIN rows2 v ON a.type=v.type
AND isnull(a.col1,'')=isnull(v.col2,'')

[Erland Sommarskog]

Martini (ma*****@online.ee) writes:

I have quite disturbing situation where I am not happy about the way how
SQL handles the query. Situation is related to using user function in
INNER JOIN select. Although the problem occured in more complex
situation the query can be simplified to following example with same
results:
...
So the AND clause after INNER JOIN is an obvious mistake. That is not the
problem, problem is how SQL reacts. On SQL 2000 SP4 the result for running
the query is following error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 101. Database may not be activated yet or may
be in transition.

On SQL 2000 SP3 the result is even more dramatic - SQL service will
consume 100% of all available processors and become unresponsive. Only
solution is to restart either SQL service or the server.

My question is if this self-destructive behavior of SQL server can be
prevented by some configuration parameters or patches. I am a bit
annoyed by the fact that developer can kill the server by little bit of
poor programming that is syntactically acceptable for server.

Obviously this is a bug, but without a complete script that demonstrates
the problem, it's difficult to suggest workarounds. (Except as Alexander
says, avoid the UDF altogether.) It would also be interesting to see
if the problem appears on SQL 2005.

I did try to compose a repro from your post, but my query did not fail.

A complete repro script must either create tables itself, or refer
to known tables, such as system tables or tables in Northwind or pubs.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Steve Kass]

Martini,

You appear to be encountering this known bug:

http://support.microsoft.com/default...b;en-us;819264

Steve Kass
Drew University

Martini wrote:

Hello all.

I have quite disturbing situation where I am not happy about the way how SQL
handles the query. Situation is related to using user function in INNER JOIN
select. Although the problem occured in more complex situation the query can
be simplified to following example with same results:

There is an user function, that can be as simple as:

FUNCTION IsItSo (@text1 nvarchar(255), @text2 nvarchar(255))
RETURNS integer

BEGIN

if isnull(@text1,'')=isnull(@text2,'')
return 1
else
return 0

return 0
END

It can be any function returning integer, this is just for example.
Then there is a query as simple as that:
SELECT person, formula FROM
(
SELECT
a.person, v.code AS formula
FROM rows1 AS a
INNER JOIN rows2 AS v ON a.type=v.type
AND dbo.IsItSo(a.type,v.type)=1
)
as formulas
tables rows1 and rows2 can contain as little as two columns for this
example - rows1 have [person] and [type], rows2 [code] and [type]. All
columns are of nvarchar type.

So the AND clause after INNER JOIN is an obvious mistake. That is not the
problem, problem is how SQL reacts. On SQL 2000 SP4 the result for running
the query is following error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 101. Database may not be activated yet or may be
in transition.

On SQL 2000 SP3 the result is even more dramatic - SQL service will consume
100% of all available processors and become unresponsive. Only solution is
to restart either SQL service or the server.


My question is if this self-destructive behavior of SQL server can be
prevented by some configuration parameters or patches. I am a bit annoyed by
the fact that developer can kill the server by little bit of poor
programming that is syntactically acceptable for server.
One more curious bit - the section of the query located between parenthesis
can be run separately without any ill effects and get actual meaningful
results:
SELECT
a.person, v.code AS formula
FROM rows1 AS a
INNER JOIN rows2 v ON a.type=v.type
AND dbo.IsItSo(a.type,v.type)=1