Re: (Fwd) Re: Any Oracle 9 users? A test please...

On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <mascarm(at)mascari(dot)com>
wrote:
> I'm wondering how the others handle multiple
>references in CURRENT_TIMESTAMP in a single stored
>procedure/function invocation.

MSSQL 7 seems to evaluate CURRENT_TIMESTAMP for each statement,
Interbase 6 once per procedure call. Here are my test procedures:

MSSQL 7
create table tst (i integer, d datetime not null)
go
create procedure tstInsert
as begin
delete from tst
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
end
go
begin transaction
exec tstInsert
commit transaction
select * from tst
i d
----------- ---------------------------
0 2002-09-30 22:26:06.540
1 2002-09-30 22:26:06.540
32 2002-09-30 22:26:06.540
243 2002-09-30 22:26:06.540
1024 2002-09-30 22:26:06.550
3125 2002-09-30 22:26:06.550
7776 2002-09-30 22:26:06.550
16807 2002-09-30 22:26:06.560
32768 2002-09-30 22:26:06.570
59049 2002-09-30 22:26:06.590

(10 row(s) affected)

Interbase 6
SQL> create table tst(i integer, d timestamp);
SQL> commit;
SQL> set term !!;
SQL> create procedure tstInsert as begin
CON> delete from tst;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> end;
CON> !!

SQL> set term ; !!
SQL> commit;
SQL> execute procedure tstInsert; -- takes approx. 5 seconds.
SQL> select * from tst;

I D
============ =========================

0 1858-11-17 00:00:00.0000
1 2002-09-30 22:37:54.0000
32 2002-09-30 22:37:54.0000
243 2002-09-30 22:37:54.0000
1024 2002-09-30 22:37:54.0000
3125 2002-09-30 22:37:54.0000
7776 2002-09-30 22:37:54.0000
16807 2002-09-30 22:37:54.0000
32768 2002-09-30 22:37:54.0000
59049 2002-09-30 22:37:54.0000

SQL> commit;

BTW, it's interesting (but OT) how they handle

select count(*), current_timestamp, 1 from tst where 0=1;

differently.

MSSQL: 0 2002-09-30 22:53:55.920 1
Interbase: 0 1858-11-17 00:00:00.0000 0 <--- bug here?
Postgres: 0 2002-09-30 21:10:35.660781+02 1

Servus
Manfred