Erroneous behaviour of COALESCE versus ISNULL??

I am getting very of why COALESCE doesnt report NULL as NULL.
Can somebody please explain the behavoiour described below?
Create these tables:
create table dbo.Warrants (
WarrantId bigint not null
constraint WarrantsPKCO primary key nonclustered (WarrantId)
)
go
create table dbo.TextLimitations (
WarrantId bigint not null,
LimitationText text not null,
constraint TextLimitationsPKCO primary key clustered (WarrantId)
)
go
EXECUTE SP_TABLEOPTION 'dbo.TextLimitations',
'TEXT IN ROW', 'ON'
go
Insert this data:
insert into dbo.Warrants values (1)
insert into dbo.Warrants values (2)
go
insert into dbo.TextLimitations values (1, 'a very long text')
go
Run this query:
SELECT W.WarrantId
,Text1 = COALESCE(LimitationText, '')
,Text2 = CASE
WHEN (LimitationText IS NOT NULL) THEN LimitationText
WHEN (LimitationText IS NULL) THEN 'it is null'
ELSE 'else null'
END
,Text3 = ISNULL(LimitationText, '')
FROM dbo.Warrants W
LEFT JOIN dbo.TextLimitations T
ON W.WarrantId = T.WarrantId
WHERE W.WarrantId = 2
go
Why is text1 and text2 NULL?
/kHi
I get
WarrantId Text1 Text2 Text3
-- -- -- --
2 it is null
Which is what I would expect. Text1 and Text3 are both empty strings (which
is different to NULL)
You may want to check version and compatibility settings.
John
"kurt sune" wrote:

> I am getting very of why COALESCE doesnt report NULL as NULL.
> Can somebody please explain the behavoiour described below?
> Create these tables:
> create table dbo.Warrants (
> WarrantId bigint not null
> constraint WarrantsPKCO primary key nonclustered (WarrantId)
> )
> go
> create table dbo.TextLimitations (
> WarrantId bigint not null,
> LimitationText text not null,
> constraint TextLimitationsPKCO primary key clustered (WarrantId)
> )
> go
> EXECUTE SP_TABLEOPTION 'dbo.TextLimitations',
> 'TEXT IN ROW', 'ON'
> go
> Insert this data:
> insert into dbo.Warrants values (1)
> insert into dbo.Warrants values (2)
> go
> insert into dbo.TextLimitations values (1, 'a very long text')
> go
> Run this query:
> SELECT W.WarrantId
> ,Text1 = COALESCE(LimitationText, '')
> ,Text2 = CASE
> WHEN (LimitationText IS NOT NULL) THEN LimitationText
> WHEN (LimitationText IS NULL) THEN 'it is null'
> ELSE 'else null'
> END
> ,Text3 = ISNULL(LimitationText, '')
> FROM dbo.Warrants W
> LEFT JOIN dbo.TextLimitations T
> ON W.WarrantId = T.WarrantId
> WHERE W.WarrantId = 2
> go
>
> Why is text1 and text2 NULL?
>
> /k
>
>|||Addendum:
on two machines I get
2 NULL NULL emptystring
on all others I get
2 emptystring it is null emptystring
What makes the first two machines answer incorrectly?
/k
"kurt sune" <apa@.apa.com> wrote in message
news:uzi99p%23xFHA.3408@.TK2MSFTNGP09.phx.gbl...
> I am getting very of why COALESCE doesnt report NULL as NULL.
> Can somebody please explain the behavoiour described below?
> Create these tables:
> create table dbo.Warrants (
> WarrantId bigint not null
> constraint WarrantsPKCO primary key nonclustered (WarrantId)
> )
> go
> create table dbo.TextLimitations (
> WarrantId bigint not null,
> LimitationText text not null,
> constraint TextLimitationsPKCO primary key clustered (WarrantId)
> )
> go
> EXECUTE SP_TABLEOPTION 'dbo.TextLimitations',
> 'TEXT IN ROW', 'ON'
> go
> Insert this data:
> insert into dbo.Warrants values (1)
> insert into dbo.Warrants values (2)
> go
> insert into dbo.TextLimitations values (1, 'a very long text')
> go
> Run this query:
> SELECT W.WarrantId
> ,Text1 = COALESCE(LimitationText, '')
> ,Text2 = CASE
> WHEN (LimitationText IS NOT NULL) THEN LimitationText
> WHEN (LimitationText IS NULL) THEN 'it is null'
> ELSE 'else null'
> END
> ,Text3 = ISNULL(LimitationText, '')
> FROM dbo.Warrants W
> LEFT JOIN dbo.TextLimitations T
> ON W.WarrantId = T.WarrantId
> WHERE W.WarrantId = 2
> go
>
> Why is text1 and text2 NULL?
>
> /k
>|||On Mon, 3 Oct 2005 09:40:53 +0200, kurt sune wrote:

>Addendum:
>on two machines I get
>2 NULL NULL emptystring
>on all others I get
>2 emptystring it is null emptystring
>
>What makes the first two machines answer incorrectly?
Hi Kurt,
Maybe an older service pack? What's the output of SELECT @.@.VERSION on
each of the machines?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||One machine that answers correctly:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
One machine that answers wrong:
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
/k
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp63k1pjr8uh3us6o63abhiirh6lhkfcra@.
4ax.com...
> On Mon, 3 Oct 2005 09:40:53 +0200, kurt sune wrote:
>
> Hi Kurt,
> Maybe an older service pack? What's the output of SELECT @.@.VERSION on
> each of the machines?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
After you have upgraded to SP3a or possibly standardised on SP4, check out
differences in the output sp_dboption and sp_dbcmptlevel for each of the
databases.
John
"kurt sune" wrote:

> One machine that answers correctly:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> One machine that answers wrong:
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
> Nov 19 2001 13:23:50
> /k
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:lp63k1pjr8uh3us6o63abhiirh6lhkfcra@.
4ax.com...
>
>|||On Tue, 4 Oct 2005 08:14:09 +0200, kurt sune wrote:

>One machine that answers correctly:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
>One machine that answers wrong:
>Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
> Nov 19 2001 13:23:50
Hi Kurt,
Upgrade all your machines to at least SP 3a (version 8.00.760) ASAP.
This will probably remove your bug. But even better is that it will cure
your current vulnerability to the SQL Slammer worm.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Unfortunately I work for a very bureaucratic company and upgrading is a
veeery slow affair.
Thanks for the tip of slammer, now I have three arguments in my
argumentation box.
(the coalesce bug, length of mail message body bug, slammer worm)
/k
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jgv5k1dh91hno6m50l1a8gtk85ufumrr66@.
4ax.com...
> On Tue, 4 Oct 2005 08:14:09 +0200, kurt sune wrote:
>
> Hi Kurt,
> Upgrade all your machines to at least SP 3a (version 8.00.760) ASAP.
> This will probably remove your bug. But even better is that it will cure
> your current vulnerability to the SQL Slammer worm.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo
Is there a KB for this COALESCE bug?
John
"Hugo Kornelis" wrote:

> On Tue, 4 Oct 2005 08:14:09 +0200, kurt sune wrote:
>
> Hi Kurt,
> Upgrade all your machines to at least SP 3a (version 8.00.760) ASAP.
> This will probably remove your bug. But even better is that it will cure
> your current vulnerability to the SQL Slammer worm.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Also have a look at
http://toponewithties.blogspot.com/...es.blogspot.com
"kurt sune" <apa@.apa.com> wrote in message
news:eovaSQXyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Unfortunately I work for a very bureaucratic company and upgrading is a
> veeery slow affair.
> Thanks for the tip of slammer, now I have three arguments in my
> argumentation box.
> (the coalesce bug, length of mail message body bug, slammer worm)
> /k
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:jgv5k1dh91hno6m50l1a8gtk85ufumrr66@.
4ax.com...
>