Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

UDF Stops Working

6 posts in General Discussion Last posting was on 2011-06-08 16:53:29.0Z
Mark Maslow Posted on 2011-06-08 00:02:30.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: UDF Stops Working
Message-ID: <MPG.28585bdb45610e34989737@forums.sybase.com>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Jun 2011 17:02:30 -0700
X-Trace: forums-1-dub 1307491350 10.22.241.152 (7 Jun 2011 17:02:30 -0700)
X-Original-Trace: 7 Jun 2011 17:02:30 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30226
Article PK: 72404

We're running ASE 15.0.3

I created a UDF that calculates a distance between two locations.

The UDF used to work fine. For some reason, it started returning null in
all cases, no matter what was passed to it.

I did a sp_helptext to get the text of the UDF, dropped the UDF and
recreated it using the results of the sp_helptext. After recompiling, it
started working again.

Does anyone have a clue as to what could cause an existing UDF to stop
working? Do UDFs always have to recompiled after some kind of events,
like DB shutdowns?

The UDF:

create function milesdistant(@lat1 numeric(12,10), @lng1 numeric(13,10),
@lat2 numeric(12,10), @lng2 numeric(13,10))
returns numeric(6,2)
as
RETURN ROUND(ACOS(
SIN(RADIANS(@lat1)) * SIN(RADIANS(@lat2))
+ COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)

) * COS(RADIANS(@lng2 - @lng1))
) * 3956.547 ,2)


Mark Maslow Posted on 2011-06-08 00:38:59.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Re: UDF Stops Working
Message-ID: <MPG.2858647e4b0d5656989738@forums.sybase.com>
References: <MPG.28585bdb45610e34989737@forums.sybase.com>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Jun 2011 17:38:59 -0700
X-Trace: forums-1-dub 1307493539 10.22.241.152 (7 Jun 2011 17:38:59 -0700)
X-Original-Trace: 7 Jun 2011 17:38:59 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30227
Article PK: 72407

In article <MPG.28585bdb45610e34989737@forums.sybase.com>,
mark.maslow@sierraclub.org says...

> We're running ASE 15.0.3
>
> I created a UDF that calculates a distance between two locations.
>
> The UDF used to work fine. For some reason, it started returning null in
> all cases, no matter what was passed to it.
>
> I did a sp_helptext to get the text of the UDF, dropped the UDF and
> recreated it using the results of the sp_helptext. After recompiling, it
> started working again.
>
> Does anyone have a clue as to what could cause an existing UDF to stop
> working? Do UDFs always have to recompiled after some kind of events,
> like DB shutdowns?
>
> The UDF:
>
> create function milesdistant(@lat1 numeric(12,10), @lng1 numeric(13,10),
> @lat2 numeric(12,10), @lng2 numeric(13,10))
> returns numeric(6,2)
> as
> RETURN ROUND(ACOS(
> SIN(RADIANS(@lat1)) * SIN(RADIANS(@lat2))
> + COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)
>
> ) * COS(RADIANS(@lng2 - @lng1))
> ) * 3956.547 ,2)
>

Just discovered that the function stops working if called with any
arguments set to null. One call with a null value in any of the
arguments results in null being returned in all subsequent calls.

Before doing the calculation, I now check the value of all arguments,
and return null if any of the values are null, without doing any
calculation. Apparently, passing any nulls to the calculation once
results in the function ceasing to work for all subsequent calls,
regardless of what's passed as arguments.

Really strange. I wonder how the Sybase engineers figured out how to do
that.


Rob V [ Sybase ] Posted on 2011-06-08 01:04:00.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: UDF Stops Working
References: <MPG.28585bdb45610e34989737@forums.sybase.com> <MPG.2858647e4b0d5656989738@forums.sybase.com>
In-Reply-To: <MPG.2858647e4b0d5656989738@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4deeca80@forums-1-dub>
Date: 7 Jun 2011 18:04:00 -0700
X-Trace: forums-1-dub 1307495040 10.22.241.152 (7 Jun 2011 18:04:00 -0700)
X-Original-Trace: 7 Jun 2011 18:04:00 -0700, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30228
Article PK: 72406


On 08-Jun-2011 02:38, Mark Maslow wrote:
> In article<MPG.28585bdb45610e34989737@forums.sybase.com>,
> mark.maslow@sierraclub.org says...
>> We're running ASE 15.0.3
>>
>> I created a UDF that calculates a distance between two locations.
>>
>> The UDF used to work fine. For some reason, it started returning null in
>> all cases, no matter what was passed to it.
>>
>> I did a sp_helptext to get the text of the UDF, dropped the UDF and
>> recreated it using the results of the sp_helptext. After recompiling, it
>> started working again.
>>
>> Does anyone have a clue as to what could cause an existing UDF to stop
>> working? Do UDFs always have to recompiled after some kind of events,
>> like DB shutdowns?
>>
>> The UDF:
>>
>> create function milesdistant(@lat1 numeric(12,10), @lng1 numeric(13,10),
>> @lat2 numeric(12,10), @lng2 numeric(13,10))
>> returns numeric(6,2)
>> as
>> RETURN ROUND(ACOS(
>> SIN(RADIANS(@lat1)) * SIN(RADIANS(@lat2))
>> + COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)
>>
>> ) * COS(RADIANS(@lng2 - @lng1))
>> ) * 3956.547 ,2)
>>
> Just discovered that the function stops working if called with any
> arguments set to null. One call with a null value in any of the
> arguments results in null being returned in all subsequent calls.
>
> Before doing the calculation, I now check the value of all arguments,
> and return null if any of the values are null, without doing any
> calculation. Apparently, passing any nulls to the calculation once
> results in the function ceasing to work for all subsequent calls,
> regardless of what's passed as arguments.
>
> Really strange. I wonder how the Sybase engineers figured out how to do
> that.

That sounds weird. Could you post an example, or send me one directly?
I'd like to look into this and raise the issue internally at Sybase if
there is indeed something odd.

Thanks in advance,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2011-06-08 15:31:50.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: UDF Stops Working
References: <MPG.28585bdb45610e34989737@forums.sybase.com> <MPG.2858647e4b0d5656989738@forums.sybase.com>
In-Reply-To: <MPG.2858647e4b0d5656989738@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4def95e6$1@forums-1-dub>
Date: 8 Jun 2011 08:31:50 -0700
X-Trace: forums-1-dub 1307547110 10.22.241.152 (8 Jun 2011 08:31:50 -0700)
X-Original-Trace: 8 Jun 2011 08:31:50 -0700, vip152.sybase.com
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30232
Article PK: 72411


On 6/7/2011 6:38 PM, Mark Maslow wrote:
> In article<MPG.28585bdb45610e34989737@forums.sybase.com>,
> mark.maslow@sierraclub.org says...
>> We're running ASE 15.0.3
>>
>> I created a UDF that calculates a distance between two locations.
>>
>> The UDF used to work fine. For some reason, it started returning null in
>> all cases, no matter what was passed to it.
>>
>> I did a sp_helptext to get the text of the UDF, dropped the UDF and
>> recreated it using the results of the sp_helptext. After recompiling, it
>> started working again.
>>
>> Does anyone have a clue as to what could cause an existing UDF to stop
>> working? Do UDFs always have to recompiled after some kind of events,
>> like DB shutdowns?
>>
>> The UDF:
>>
>> create function milesdistant(@lat1 numeric(12,10), @lng1 numeric(13,10),
>> @lat2 numeric(12,10), @lng2 numeric(13,10))
>> returns numeric(6,2)
>> as
>> RETURN ROUND(ACOS(
>> SIN(RADIANS(@lat1)) * SIN(RADIANS(@lat2))
>> + COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)
>>
>> ) * COS(RADIANS(@lng2 - @lng1))
>> ) * 3956.547 ,2)
>>
>
> Just discovered that the function stops working if called with any
> arguments set to null. One call with a null value in any of the
> arguments results in null being returned in all subsequent calls.
>
> Before doing the calculation, I now check the value of all arguments,
> and return null if any of the values are null, without doing any
> calculation. Apparently, passing any nulls to the calculation once
> results in the function ceasing to work for all subsequent calls,
> regardless of what's passed as arguments.
>
> Really strange. I wonder how the Sybase engineers figured out how to do
> that.

Do you have statement cache turned on? If so, do you get the
same behavior with statement cache turned off?


Bret Halford Posted on 2011-06-08 16:14:21.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: UDF Stops Working
References: <MPG.28585bdb45610e34989737@forums.sybase.com> <MPG.2858647e4b0d5656989738@forums.sybase.com> <4def95e6$1@forums-1-dub>
In-Reply-To: <4def95e6$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4def9fdd$1@forums-1-dub>
Date: 8 Jun 2011 09:14:21 -0700
X-Trace: forums-1-dub 1307549661 10.22.241.152 (8 Jun 2011 09:14:21 -0700)
X-Original-Trace: 8 Jun 2011 09:14:21 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30233
Article PK: 72412


> Do you have statement cache turned on? If so, do you get the
> same behavior with statement cache turned off?
>
>

Never mind, it isn't the statement cache.

I simplified the function down to the following, the critical
bit seems to be the radians function, I tried substituting in
various other functions and did not see the problem for anything
but radians.

create function f1(@p1 numeric(12,10))

returns numeric(6,2)
as
return ROUND( radians(@p1),2)
go

select dbo.f1(12)
go
select dbo.f1(null)
go
select dbo.f1(12)
go

I've opened CR 673179 for the issue. If you have a support contract,
I recommend opening a case and requesting a fix for the CR, as a
business case will improve it's priority in engineering.

Cheers,
-bret


Mark Maslow Posted on 2011-06-08 16:53:29.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Re: UDF Stops Working
Message-ID: <MPG.28594886b83bab2989739@forums.sybase.com>
References: <MPG.28585bdb45610e34989737@forums.sybase.com> <MPG.2858647e4b0d5656989738@forums.sybase.com> <4def95e6$1@forums-1-dub> <4def9fdd$1@forums-1-dub>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Jun 2011 09:53:29 -0700
X-Trace: forums-1-dub 1307552009 10.22.241.152 (8 Jun 2011 09:53:29 -0700)
X-Original-Trace: 8 Jun 2011 09:53:29 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30234
Article PK: 72414

In article <4def9fdd$1@forums-1-dub>, bret@sybase.com says...

> I simplified the function down to the following, the critical
> bit seems to be the radians function, I tried substituting in
> various other functions and did not see the problem for anything
> but radians.
>
> create function f1(@p1 numeric(12,10))
>
> returns numeric(6,2)
> as
> return ROUND( radians(@p1),2)
> go
>
> select dbo.f1(12)
> go
> select dbo.f1(null)
> go
> select dbo.f1(12)
> go
>
> I've opened CR 673179 for the issue. If you have a support contract,
> I recommend opening a case and requesting a fix for the CR, as a
> business case will improve it's priority in engineering.
>
> Cheers,
> -bret
>

Thanks.

Fortunately, now that we know about this behavior, we have a workaround
- just be sure to check all parameters for null, and return null if any
found, before doing the calculation.

Sure is strange behavior - I've never before encountered a case where
calling a function with a particular value modified the behavior of the
function in subsequent calls. There are obviously things about the way
Sybase implements UDFs that I don't understand.

You seem to have isolated the problem well. I'm passing your information
on to our Sybase support contact.

Thanks again.