Re: SQL 2000 fails to detect NULL values

From: Steve Kass (skass_at_drew.edu)
Date: 10/07/04

  • Next message: Yaocuauhtzin: "Re: SQL 2000 fails to detect NULL values" 
    Date: Thu, 07 Oct 2004 11:54:35 -0400

    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: Yaocuauhtzin: "Re: SQL 2000 fails to detect NULL values"

    Relevant Pages

    • Re: SQL 2000 fails to detect NULL values
      ... I would expect this to be possible even for a dynamically-generated query. ... or they may help you find a workaround. ... >>You are encountering a known bug. ... >>that the column value after the outer join is the constant it's defined ...
      (microsoft.public.sqlserver.mseq)
    • Left Outer Join: Index Seek not providing all index columns
      ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
      (microsoft.public.sqlserver.server)
    • Re: Cond. Formatting w/ DLookup
      ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
      (microsoft.public.access.formscoding)
    • Re: Optimising the Query
      ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
      (comp.databases.oracle.misc)
    • Re: Cond. Formatting w/ DLookup
      ... I'd like to be able to indicate on the first form, ... IvId field, then a better way would be to change the first ... form's record source to be a query with an outer join of the ... Using your excellent idea of the outer join, ...
      (microsoft.public.access.formscoding)