Re: SQL 2000 fails to detect NULL values
From: Yaocuauhtzin (Yaocuauhtzin_at_discussions.microsoft.com)
Date: 10/07/04
- Previous message: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- In reply to: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Next in thread: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Reply: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 7 Oct 2004 15:01:03 -0700
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)
> >
> >
> >
>
- Previous message: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- In reply to: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Next in thread: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Reply: Steve Kass: "Re: SQL 2000 fails to detect NULL values"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
