Re: SQL 2000 fails to detect NULL values

From: Yaocuauhtzin (Yaocuauhtzin_at_discussions.microsoft.com)
Date: 11/29/04 

Date: Mon, 29 Nov 2004 14:23:03 -0800

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.

Yours comments, please.

By the way, MS support takes 3 weeks to find the bug.

"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