Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
markus
On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default...b;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com... [vbcol=seagreen]
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
'case_jn'
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default...b;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)