Re: SQL 2000 fails to detect NULL values

From: Steve Kass (skass_at_drew.edu)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 00:05:51 -0500

Yaocuauhtzin wrote:

>Thanks, I decide to open a support case and MS support service determined
>it's a different IsNull-bug (you was right, no-charge support in this case).
>
>The MS' solution is not a hot fix, but they change my code to work around
>this bug. They use COALESCE function to detect Null values.
>
>
This is probably fine. ISNULL and COALESCE are almost, but not quite,
the same. ISNULL is not an ANSI standard SQL function, and probably
gets optimized differently.

>Yours comments, please.
>
>
>By the way, MS support takes 3 weeks to find the bug.
>
>
It's good practice for them. ;)

It's also good for other Microsoft customers when these bugs make it to
support cases. Without support cases, bugs are less likely to be fixed
or addressed in a Knowledge Base article.

SK

>
>"Steve Kass" wrote:
>
>
>
>>Without seeing what your queries look like, I only have a couple more
>>suggestions beyond the workaround ideas I already suggested:
>>
>>1. Replace any constant column in a derived table with something that
>>the optimizer can't recognize as constant. For example, replace
>>.... (
>> select 'constant' as c
>>) T
>>
>>with
>>.... (
>> select substring('constant',1,8+ascii(getdate())/1000) as c
>>) ...
>>
>>I would expect this to be possible even for a dynamically-generated query.
>>
>>2. Open a support case for this. While I can't guarantee anything, I
>>do believe Microsoft generally refunds any support charges when the
>>problem is determined to be a bug. You may get a hotfix if there is
>>one, or they may help you find a workaround.
>>
>>SK
>>
>>Yaocuauhtzin wrote:
>>
>>
>>
>>>I need more help. This query is a simplyfied that shown my problem.
>>>
>>>The real query is 1.68 KB long, 4th level subquery with intraquery
>>>parallelism.
>>>The query is make in run-time in separate blocks, according to configurable
>>>options.
>>>The application that do that have a lot of querys like this.
>>>
>>>I have work with the application aprox. 20 months on SQL7, and I use SQL2000
>>>for a week with bad results.
>>>
>>>I've read a the MSKB article 321541. That bug was fix in sp3 (sql ver
>>>8.00.613) that I already have (my sql ver 8.00.760). I made the test describe
>>>in the article and work normally, but problem still. I can't trust sql2000
>>>this way
>>>
>>>I need to change to SQL2000, please help me.
>>>
>>>thanks
>>>
>>>
>>>"Steve Kass" wrote:
>>>
>>>
>>>
>>>
>>>
>>>>You are encountering a known bug. A workaround for your query is to
>>>>write the subquery in an equivalent form:
>>>>
>>>>SELECT B.ID, ISNULL(B.ID,'Is Null')
>>>>
>>>>
>>>>FROM secuenciales A
>>>
>>>
>>>>LEFT JOIN (
>>>> select 'Isnt Null' id
>>>>) B ON 1=0
>>>>
>>>>Here is a longer answer I gave a while back about this bug:
>>>>
>>>>This is a known bug, though I am not aware of a knowledge base article
>>>>about it. It occurs when a view or derived table on the "non-outer"
>>>>side of an OUTER JOIN contains a constant column, and that column
>>>>appears in the WHERE condition. The query processor wrongly assumes
>>>>that the column value after the outer join is the constant it's defined
>>>>to be in the derived table or view. I've always been able to use one of
>>>>the following 3 workarounds:
>>>>
>>>>1. If the view or derived table X has a non-null, non-constant column
>>>>[other_column], replace the failing condition with (X.other_column is null).
>>>>2. Replace the constant column const_col in the where clause by an
>>>>equivalent value that is an expression, not a simple column reference,
>>>>by adding 0 or concatenating a space character.
>>>>3. If there is no non-constant column in the derived table or view, as
>>>>in your case, you don't need an outer join. Instead of an outer join
>>>>with (select 1 as a, 'this' as b), write the query using NOT EXISTS or
>>>>EXISTS or IN.
>>>>
>>>>select blah
>>>>
>>>>
>>>>from T left outer join (
>>>
>>>
>>>> select 1 as a, 'this' as b, real_col from U
>>>>) V
>>>>on real_col = T.blah and T.other = a
>>>>where b <> 'this'
>>>>
>>>>can probably be rewritten something like this
>>>>
>>>>select blah
>>>>
>>>>
>>>>from T
>>>
>>>
>>>>where not exists (
>>>> select * from U
>>>> where real_col = T.blah
>>>> and T.other = 1
>>>>)
>>>>
>>>>-- Steve Kass
>>>>-- Drew University
>>>>-- Ref: AA008851-E792-429A-8C73-13E50AE94E32
>>>>
>>>>Yaocuauhtzin wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I have this query in SQL 2000, I get wrong answer
>>>>>
>>>>>SELECT B.ID, ISNULL(B.ID,'Is Null')
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>FROM secuenciales A
>>>>
>>>>
>>>>
>>>>
>>>>>LEFT JOIN (
>>>>>select b.id
>>>>>from (select 'Isnt Null' id) b
>>>>>LEFT join secuenciales d on 1=0
>>>>>) B ON 1=0
>>>>>
>>>>>
>>>>>ID
>>>>>--------- ---------
>>>>>NULL Isnt Null
>>>>>
>>>>>(1 row(s) affected)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>

Relevant Pages