Peculiar ISNULL behavior on field in implicit table
-
Hopefullly somebody can help explain what's going on here. Consider the following simple example that I created to illustrate my problem:
*********************************
create table #x
(fld1 int)
create table #y
(fld1 int)
insert into #x values (1)
insert into #x values (2)
insert into #x values (3)
insert into #x values (4)
insert into #x values (5)
insert into #y values (1)
insert into #y values (3)
insert into #y values (4)
select #x.fld1, t.matches, isnull(t.matches,'No match found')
from #x
left join
(select fld1, matches = 'Match found' from #y) t
on #x.fld1 = t.fld1
********************************
I get output as follows:
fld1 matches
------ ----------- -----------
1 Match found Match found
2 NULL Match found
3 Match found Match found
4 Match found Match found
5 NULL Match found
Can anybody tell me why I'm getting the "false positives" out of the ISNULL statement?
Thanks.
-
Maybe something to do with your server or database settings.
When I run your query I get,
fld1 matches ----------- ----------- ----------- 1 Match found Match found 2 NULL No match fo 3 Match found Match found 4 Match found Match found 5 NULL No match fo
--------------------
Colt 45 - the original point and click interface
-
Hmm. So it would seem. Any ideas as to what settings might cause this sort of weird behavior?
-
Take a look at the ANSI settings. If you run profiler you'll see what gets set for the connection.
--------------------
Colt 45 - the original point and click interface -
Have you upgraded to SP4? I remember hearing that there was some kind of NULL-related bug in SP3a. I do not know if this was it though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply