Re: SQL 2000 fails to detect NULL values
From: Steve Kass (skass_at_drew.edu)
Date: 10/09/04
- Next message: Paul Nielsen: "Re: Consolidate Rows on Select"
- Previous message: nemb: "Re: IIS Logs Max Per Hour"
- In reply to: Yaocuauhtzin: "Re: SQL 2000 fails to detect NULL values"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 09 Oct 2004 15:12:57 -0400
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)
>>>
>>>
>>>
>>>
>>>
- Next message: Paul Nielsen: "Re: Consolidate Rows on Select"
- Previous message: nemb: "Re: IIS Logs Max Per Hour"
- In reply to: Yaocuauhtzin: "Re: SQL 2000 fails to detect NULL values"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
