Is the function "isnull" changed in sybase 15?

[Kapil]

Hi All

Is the function "isnull" changed in sybase 15?

execute the following code in 12.5 and 15.0 and you would see the
difference in tab1 table description for col1 column. The datatype is
converted to varchar in 12.5 as well as 15. However, the isnull
function changes null column from 0 to 1 from tab1 table col1 column
in 15 but not in 12.5.

Can someone tell me, is there a workaround for this?

use tempdb
go
create table abcd ( x char(1) null, y char(1) )
go
sp_help abcd
go
insert into abcd values( 'a','a')
insert into abcd values( 'b','b')
insert into abcd values( 'c','c')
insert into abcd values( 'd','d')
go
select * into xyz from abcd
go
sp_help xyz
go
insert into abcd (y) values('e')
insert into abcd (y) values('f')
go
select * from abcd
go
select isnull(c.x, 'Z') col1, y as col2
into tab1
from abcd c
go
sp_help tab1
go

[Sherlock, Kevin [TeamSybase]]

For what it's worth, the 15.0 behavior as you describe it makes the most
sense to me.

If you want an explicit datatype to be made, well, explicity ask for it:

select convert(varchar(1) not null,isnull(c.x, 'Z')) as col1, y as col2

into tab1
from abcd c

"Kapil" <kapila....@gmail.com> wrote in message
news:cd6f4ac3-4b00-45c1...@e23g2000vbe.googlegroups.com...

[Bret Halford [Sybase]]

Kapil wrote:
> Hi All
>
> Is the function "isnull" changed in sybase 15?
>
> execute the following code in 12.5 and 15.0 and you would see the
> difference in tab1 table description for col1 column. The datatype is
> converted to varchar in 12.5 as well as 15. However, the isnull
> function changes null column from 0 to 1 from tab1 table col1 column
> in 15 but not in 12.5.
>
> Can someone tell me, is there a workaround for this?
>

The behavior did change in 15.0.2 #6 as a side-effect of the
fix for CR 497066 (a performance enhancement),
in that the result of isnull() used to be
a nullable type and is now a non-nullable type.

In 15.0.3 ESD #1, traceflag 15336 was introduced to turn off
the changes from CR 497066, restoring the isnull() behavior
but losing the performance enhancment.

CR 569947 is open and planned for 15.0.3 ESD #3 which would
do away with the need for the traceflag; isnull behavior
would be as it was originally and the performance enhancement
would still work.

There is a bit of interesting history behind this - as Kevin
noted, it doesn't make much intuitive sense for isnull() to return a
null in the first place. However, the original implementation did, and
prior to 12.0 isnull(column, NULL) was used as a common method to make
a column in a SELECT INTO table nullable. In 12.0, the convert()
function was enhanced to allow specification of nullability,
providing a more obvious way of doing it.

-bret

[Bret Halford [Sybase]]

Kapil wrote:
> Hi All
>
> Is the function "isnull" changed in sybase 15?
>
> execute the following code in 12.5 and 15.0 and you would see the
> difference in tab1 table description for col1 column. The datatype is
> converted to varchar in 12.5 as well as 15. However, the isnull
> function changes null column from 0 to 1 from tab1 table col1 column
> in 15 but not in 12.5.
>
> Can someone tell me, is there a workaround for this?
>

[Michael Peppler [Team Sybase]]

On Thu, 21 May 2009 15:02:38 -0700, Bret Halford [Sybase] wrote:

> Kapil wrote:
>> Hi All
>>
>> Is the function "isnull" changed in sybase 15?
>>
>> execute the following code in 12.5 and 15.0 and you would see the
>> difference in tab1 table description for col1 column. The datatype is
>> converted to varchar in 12.5 as well as 15. However, the isnull
>> function changes null column from 0 to 1 from tab1 table col1 column
>> in 15 but not in 12.5.
>>
>> Can someone tell me, is there a workaround for this?
>>
>
>

> There is a bit of interesting history behind this - as Kevin
> noted, it doesn't make much intuitive sense for isnull() to return a
> null in the first place. However, the original implementation did, and
> prior to 12.0 isnull(column, NULL) was used as a common method to make
> a column in a SELECT INTO table nullable. In 12.0, the convert()
> function was enhanced to allow specification of nullability,
> providing a more obvious way of doing it.

Another use for isnull(col, null) that I have seen is in a join statement
where both sides of the condition are supposed to be null. As you can't
write

select ....
from tablea a, tableb b
where ...conditions...
and a.col = b.col

and have the condition work if both a.col and b.col are null (yes, I know
- bad design), you can work around this (assuming ANSI null is off)

The workaround was

select ...
from tablea a , tableb b
where ... conditions ...
and a.col = isnull(b.col, null)

which really threw me the first time I saw it.

As we're in the process of upgrading from 12.5.x to 15.x I'm now going to
have to go back and check (or fix - probably better!) that code.

Thanks,

Michael

[Kapil]

Thanks Bret for this information. It was quite useful. Thanks all for
your responses.

[dejandiom]

Wow! You are to lucky that a certain prima-donna from AU
does not live here in this newsgroups any more. He would
have eat you for lunch now...

[Sherlock, Kevin [TeamSybase]]

<Dejandio M> wrote in message news:4a16fca7.28d...@sybase.com...

Nahh. Anthony would never do any such thing! ;)

[Michael Peppler [Team Sybase]]

:-)

Not to mention that this isn't something that I actually approve of. But
when you join a new team where there is a lot of pre-existing code you
don't go changing everything that you personally find potentially
incorrect...

Michael