sql server programming: IsNull/Coalesce bug in Yukon? -- sql server programming

IsNull/Coalesce bug in Yukon?

Morgan
4/1/2006 11:18:09 PM

sql server programming:

Shouldn't this return -1? I don't have a SQL Server 2000 instance to compare
to, but doesn't this look like a bug?

Thanks,

Morgan

create table Test(col1 int identity not null primary key, col2 varchar(9))

go

insert into Test(col2)values('test2')

go

Select coalesce(col1, -1)

From Test (nolock)

Where col2 = ''

go

Select isnull(col1, -1)

From Test (nolock)

Where col2 = ''

Re: IsNull/Coalesce bug in Yukon?

Steve Kass
4/2/2006 12:03:22 AM

Morgan,

The behavior you see (no rows returned) is the correct behavior.
No rows satisfy WHERE col2 = '', therefore no rows are returned.

There are situations where you can get a result when there
are no matching rows, but this is not one of them. (One of
them is to select MAX(col1) instead of col1. When there
are no rows, the MAX of a column is NULL, the COUNT
is zero, and so on.)

Steve Kass
Drew University

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.