The following UPDATE statement:

update big_table
set col10 = current_timestamp
where col1 between 1 and 1000000

produced 4.898 different update values for the col10 column.

I'd expect that the CURRENT_TIMESTAMP function is only evaluated once during
the execution of an SQL statement, such that all the rows affected by the
update are assigned the same value.

Ole Willy Tuv

Ole,

Ole Willy Tuv wrote:
> The following UPDATE statement:
>
> update big_table
> set col10 = current_timestamp
> where col1 between 1 and 1000000
>
> produced 4.898 different update values for the col10 column.
>
> I'd expect that the CURRENT_TIMESTAMP function is only evaluated once
> during the execution of an SQL statement, such that all the rows
> affected by the update are assigned the same value.

I think that here the same logic applies as with CURRENT_GUID or random
numbers.

Ralf

Ralf,

<< I think that here the same logic applies as with CURRENT_GUID or random
numbers. >>

The CURRENT_TIMESTAMP function was not evaluated for each row, rather 4.898
times during the update of 1 million rows.

I did a quick check with SQL Server, which evaluates functions like
CURRENT_TIMESTAMP, RAND etc. only once during the execution of an SQL
statement (general SQL rule I think). SQL Server only allows the
NEWSEQUANTIALID function (similar to EDB's CURRENT_GUID function) to be used
in a column DEFAULT expression, which IMO makes sense.

Ole Willy Tuv

Ole,

<< produced 4.898 different update values for the col10 column. >>

How can it produce 4.898 different values ?

<< I'd expect that the CURRENT_TIMESTAMP function is only evaluated once
during the execution of an SQL statement, such that all the rows affected by
the update are assigned the same value. >>

I'll have to double-check this.   It may mean that we need to stop treating
the CURRENT_* functions like functions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< How can it produce 4.898 different values ? >>

He he, localized thousand separator. How about 4,898 different values ?

<< I'll have to double-check this.   It may mean that we need to stop
treating the CURRENT_* functions like functions. >>

I think functions generally should be evaluated once during the execution of
an SQL statement.

Ole Willy Tuv

Ole Willy Tuv wrote:
....
> I think functions generally should be evaluated once during the
> execution of an SQL statement.

Should be discussed. I can think of some reasons to calc a random number for
each record. It's not pretty, but maybe Tim can add two different
parameters: CURRENT_FIX_TIMESTAMP and CURRENT_TIMESTAMP, same for similar
parameters/function. Not the best names, yes.

Ralf

Ralf,

<<< I think functions generally should be evaluated once during the
execution of an SQL statement. >>>

<< Should be discussed. I can think of some reasons to calc a random number
for each record. It's not pretty, but maybe Tim can add two different
parameters: >>

What I said about functions generally should be evaluated once is nonsense,
of course. While the general rule is that datetime functions shall be
evaluated once during the execution of an SQL statement, functions in
general obviously need to be evaluated for each row.

Ole Willy Tuv

Ole,

<< I think functions generally should be evaluated once during the execution
of an SQL statement. >>

As Ralf stated, I also did some thinking about this and frankly, it could go
either way depending upon the now infamous "user expectations".  

The case in point I came up with in my head was a situation where an UPDATE
statement used a CURRENT_GUID function to assign a new GUID to each row.
IOW, I'm not sure how to handle this to satisfy both needs (per-row and
per-statement).

Also, I checked and I definitely can't handle the CURRENT* functions as
constants instead.  That would break a lot of functionality.  So, they have
to stay as functions and return a new value every time they are called.  The
issue is how many times they are called in certain situations.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< As Ralf stated, I also did some thinking about this and frankly, it could
go either way depending upon the now infamous "user expectations".  >>

I'm not sure what you mean by "infamous user expectations". Personally, I'm
working with quite many database engines. My expectations are based on both
general SQL rules and the industry standard, i.e. the results and behavior
I'm seeing in the majority of well-known database engines. That's why I take
the time to check other engines when I'm seeing unexpected (to me) results
and behavior.

I acknowledge that EDB users coming from a DBISAM background might have
other expectations, due to how DBISAM works.

Ole Willy Tuv

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:F6BFDB20-6A8E-46F8-87E2-0500DCC734AE@news.elevatesoft.com:

> The issue is how many times they are called in certain
> situations.
>

Tim,

 Isn't it just possible that the function was called 1 million times, but
the value returned only changed every 204th call because the processing was
so fast?

So the issue is once more call EVERY TIME, or call ONCE per statement?

I vote for EVERY TIME, and using a parameter or a join on a temporary table
if we only want a single value.
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)