Re: (Fwd) Re: Any Oracle 9 users? A test please... - Mailing list pgsql-hackers

On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <mascarm@mascari.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,
tstc, 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,
tstd, 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,
tste insert into tst(i, d) select count(*),CURRENT_TIMESTAMP                       from tst a, tst b, tst c, tst d, tst
einsert into tst(i, d) select count(*),CURRENT_TIMESTAMP                       from tst a, tst b, tst c, tst d, tst e
insertinto tst(i, d) select count(*),CURRENT_TIMESTAMP                       from tst a, tst b, tst c, tst d, tst e
insertinto 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-3022: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

ServusManfred