Bug...? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bug...?

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-03-23 : 16:27:44

All -

Try this code out and let me know if I am losing my mind...

I found this on: 2000 SP4
And it is even worse on: 2005 SP1

I think this is crappy though:

Select
isnull(nullif(ltrim(''),''),'xx9xx'),
isnull(nullif('1234','1234'),'xx9xx'),
isnull(null,'xx9xx')

Select
coalesce(nullif(ltrim(''),''),'xx9xx'),
coalesce(nullif('1234','1234'),'xx9xx'),
coalesce(null,'xx9xx')

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-23 : 16:55:58

The issue is how coalesce and isnull handle the datatype of the parameters.

Select
a=isnull(nullif(ltrim(''),''),'xx9xx'),
b=isnull(nullif('1234','1234'),'xx9xx'),
c=isnull(null,'xx9xx')
into isnl

Select
a=coalesce(nullif(ltrim(''),''),'xx9xx'),
b=coalesce(nullif('1234','1234'),'xx9xx'),
c=coalesce(null,'xx9xx')
into coal
go

exec sp_msHelpColumns isnl
exec sp_msHelpColumns coal
go

drop table isnl,coal

rockmoose

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-23 : 17:08:27

Crazy as it may seem it isn't a bug, it works exactly the way it is documented to work. I'm not saying it makes sense necessarily, just that it does work the way it is documented to work!

Take a careful look at the docs for ISNULL, NULLIF and COALESCE. You'll see that the return type for ISNULL and NULLIF is determined by the type of the first argument, whereas the return type for COALESCE is determined from all of the arguments (it says that they must all be the same type or be implicitly convertible to the same type).

So what happens in your queries is this - SQL Server evaluates the first argument
nullif(ltrim(''),'')
and determines that its type is an empty varchar, because although it will actually return NULL, the type is still determined by the type of the first argument, and that is the type of
ltrim('')
which is an empty varchar.
So now the type of isnull(nullif(ltrim(''),''),'xx9xx') is empty varchar because that's the type of the isnull first argument. So then when the second argument ('xx9xx') is returned, it is first converted into the type of the first argument - empty varchar - and so 'xx9xx' becomes ''.
The same applies to the send one, except that the final conversion is to the same type as '1234', so 'xx9x' is returned.

To show that the type is specifically determined by the first argument, take a look at the error you get from this
select isnull(nullif(6,6),'xx9xx')
First argument of nullif (6) is int, so therefore first argument of isnull is int, but that's incompatible with the second argument of isnull. That's because, isnull has to know before it runs that it will definitely return either an int OR a varchar.

Coalesce on the other hand, looks at all of its arguments to determine what type it will return. So it sees the empty varchar, but it also sees the varchar(5) and so it sets its return type as varchar(5) because it can implicitly convert an empty varchar into a varchar(5).

The thing to remember here is that the type of the value that might be NULL is still used in determining the return type of the whole expression whether it actually turns out to ne NULL or not.

One final note - as well as the strange behaviour you can get from ISNULL, it is not ANSI standard, COALESCE is; and COALESCE can do all that ISNULL can, and more (lots of arguments). Probably the only reason people use ISNULL so much is that it's simpler and easier to remember, also depending on who you ask ISNULL is a teeeeeeeeny bit faster, but not enough to warrant using it (see http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx)
So, all that to say - use COALESCE instead of ISNULL.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-03-23 : 17:16:56

Nice explanation snSQL!

Casting to varchar(0)... Priceless!

NULL... making nothing a pain in the arse!

DavidM

Production is just another testing cycle

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-03-23 : 17:19:13

That was a great explanation... and it a crazy a$$-backwards way... it makes sense.

However, I did come to the same conclusion about coalesce vs. isnull when I ran into this earlier.

PS I tested the explanation and recieved consistent results:

Select
a=isnull(nullif(ltrim(Replicate(' ',10)+''),''),'xx9xx'),
b=isnull(nullif('1234','1234'),'xx9xx'),
c=isnull(null,'xx9xx')

Thanks alot!!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-23 : 17:38:26

Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-24 : 04:50:01

quote:
Originally posted by snSQL

Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!

totally agree there.

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-03-24 : 07:01:47

My two-penny-worth: 7thNight has been using that SIG for ages ... whilst I don't approve I think that a bunch of Thought Police spoils the fun for the rest.

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-28 : 22:02:51

quote:
Originally posted by Kristen

My two-penny-worth: 7thNight has been using that SIG for ages ... whilst I don't approve I think that a bunch of Thought Police spoils the fun for the rest.

Kristen

My wife happened to read this thread and agrees with you. she scolded me for being a thought policeman!

www.elsasoft.org

DestinyJack
Starting Member

22 Posts

Posted - 2007-04-01 : 22:48:11

Just found out this, try to run both statement a and b:

Statement a
===========
declare @arg1 varchar(20)

select @arg1 = 'a'
select isnull(nullif(@arg1, 'a'), '12345')

Statement b
===========
select isnull(nullif('a', 'a'), '12345')

It seems like if you declare the variable properly and use variable instead of pure text isnull will not give you trouble...

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-04-01 : 23:25:48

--try executing this code

declare @arg1 varchar(20)

select @arg1 = 'a'
select isnull(nullif('a',@arg1), '12345')

select isnull(nullif(@arg1,'a'), '12345')

select isnull(nullif('a', 'a'), '12345')

TCC

DestinyJack
Starting Member

22 Posts

Posted - 2007-04-01 : 23:29:11

lol, we better avoid using isnull then...

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-04-01 : 23:36:02

if your using variable in a nullif function you should put the variable as the first parameter of the function.

TCC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-04-02 : 08:11:45

quote:
Originally posted by Kristen

My two-penny-worth: 7thNight has been using that SIG for ages ... whilst I don't approve I think that a bunch of Thought Police spoils the fun for the rest.

Kristen

Kristen - Thanks for the backing

... though the complaints really matter naught to me.

I did enjoy seeing my sig after being absent for wa while. I have switched jobs... and the aforementioned co-worker has followed.

snSQL... I hope you enjoy my new sig.

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 09:09:07

And what about this?

SELECT ISNULL(NULLIF(LTRIM(''),''), 1)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-04-02 : 09:14:58

Coalesce still works:

SELECT coalesce(NULLIF(LTRIM(''),''), 0)

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 09:21:07

Yes, that's true.

But why an asterisk as an output when using ISNULL(). As per snSQL's explaination, this should also result in Blank output.

Is this has to something with error due to implicit conversion from int to varchar?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-04-02 : 09:28:58

thats what I was assuming... although, assuming is what brought this up in the first place

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-02 : 11:16:50

I'm famous! - I always wanted to be quoted by someone

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-02 : 11:20:02

quote:
Originally posted by Seventhnight

thats what I was assuming... although, assuming is what brought this up in the first place

No assumptions necessary, see Books Online (in the topic about the CAST and CONVERT functions)
"When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table."
The table shows that conversion from an int, smallint or tinyint to a char or varchar that is too short, results in * being returned.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 11:22:40

Thanks snSQL. I need to have close look at BOL.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources