Posted by Martini on 08/22/06 14:15

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

[Back to original message]