cancel
Showing results for 
Search instead for 
Did you mean: 

getdate() vs. current_date() in a user-defined function in ASE 15.0.3

Former Member
0 Kudos

Hi.

ASE 15.0.3 apparently doesn't allow getdate() inside a UDF. I thought I understood why: deterministic vs. nondeterministic blah blah blah. (I've worked around this by creating a view that selects getdate() and then letting the UDF select from that view, but that's not relevant to my question.)

I just discovered that current_date() and current_time() ARE allowed in a UDF. Aren't those just as nondeterministic as getdate()? What's up with that?

Thanks.

- John.

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

This is just a simple bug.  The ASE parser has a list of the non-deterministic functions that it should disallow when creating a user defined function.  When the new deterministic functions getutcdate(), current_date(), and current_time() were added to ASE, they were not added to the list of functions not allowed in UDFs.  Change Request (CR) 727121 is open on this, but is considered very low priority.

-bret

Former Member
0 Kudos

Thanks Bret!

Could you explain why a non-deterministic function such as getdate() is a problem inside a function?

Former Member
0 Kudos

Hi Bret,

Thanks for the explanation. So, if I am currently using current_date() in a UDF, merely because ASE 15.0.3 allows it, then I assume that would be a "bad idea". Because eventually I will be on a version of ASE where this is no longer allowed and my UDF will then be broken.

Thanks.

- John.

former_member188958
Active Contributor
0 Kudos

Hi John,

Strictly speaking, yes, I'd be cautious about making large bets that current_date() would continue to work.  However, engineering doesn't seem to be placing much priority on fixing the issue.

It also appears that support for non-deterministic user defined functions has already been implemented (with the addition of some syntax explicitly declaring the function to be non-deterministic) for the next major release, though I can't officially guarantee that the feature will become available.

My best guess is that current_date() will continue to be usable as-is at least until the new feature comes out.

-bret

former_member188958
Active Contributor
0 Kudos

I haven't been able to find a clear explantion of why non-deterministic functions were considered a problem, just a few hints that it had something to do with optimization or execution.  My guess is that additional infrastructure was needed to ensure that non-deterministic functions are always recognized as such so that the execution engine never evaluates the function more than once per row.  It appears (though I can't guarantee it) that non-deterministic user defined functions will be supported in the next major release, but must be explicitly declared as non-deterministic.

-bret

Answers (1)

Answers (1)

Former Member
0 Kudos

It is a very good question. Hope someone who has access to the code will tell us the difference.

I confirm the UDF works perfectly:

create function dbo.test

returns datetime

as

begin

declare @res datetime

select @res=convert(char(12), current_date()) + convert(char(8), current_time())

return @res

end

go

select dbo.test()

go

-------------------

Aug 28 2013  3:02PM