|
|
Posted by Steve Kass on 08/23/06 04:47
Martini,
You appear to be encountering this known bug:
http://support.microsoft.com/default.aspx?scid=kb;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
>
>
>
>
>
Navigation:
[Reply to this message]
|