Subject Re: [firebird-support] Re: Making evaluation of UDFs constant across a query
Author Mike Pomraning
On Mon, Mar 21, 2011 at 2:08 PM, Michael Ludwig <milu71@...> wrote:
> Helen Borrie schrieb am 22.03.2011 um 07:08 (+1300):
> > Date literal 'NOW' context is the executing statement
> > UDF GetExactTimestamp() refreshes with each call to the function
> I don't get it.
> select
> current_timestamp,
> cast( 'now' as timestamp )
> from rdb$database;
> Both values are always identical, as I thought.

Not always, at least under 2.1 for me. I find CURRENT_TIMESTAMP is
constant across a query, TIMESTAMP 'now' is constant across a query
but not always equal to CURRENT_TIMESTAMP, and CAST('now' ...) is
evaluated anew for every row.

The following calls each of these constructs twice, after a 1 second delay:

SQL> select current_timestamp as "current_timestamp",
CON>        timestamp 'now' as "timestamp 'now'",
CON>        cast('now' as timestamp) as "cast(now)"
CON>   from (select fb_sleep(1) from rdb$database
CON>          union all
CON>         select fb_sleep(1) from rdb$database) d(ignored);

current_timestamp 2011-03-21 16:19:28.2360
timestamp 'now' 2011-03-21 16:19:28.2350 <-- different
cast(now) 2011-03-21 16:19:29.2360 <--
different from both of the preceding

current_timestamp 2011-03-21 16:19:28.2360 <-- same as before
timestamp 'now' 2011-03-21 16:19:28.2350 <-- same as before
cast(now) 2011-03-21 16:19:30.2360 <-- one second later!

(FB_SLEEP() is a contrivance to pause execution for a second, to make
the repeated evaluation of time-functions obvious.)