Evaluation of local_time in DBPs

Roy Hann

unread,

Mar 27, 2013, 6:57:00 AM3/27/13

to

In Ingres II 10.1.0 (int.w32/105) at least, and maybe others, it looks
like local_time gets evaluated just once in a DBP. Is that intentional?

create procedure local_time_test as
declare
start_time time(9) not null;
end_time time(9) not null;
elapsed_time varchar(80) not null;
nrows integer not null;
begin
select local_time into :start_time;
select count(t.table_name||c.column_name) into :nrows
from iitables t join iicolumns c
on t.table_name = c.table_name
and t.table_owner = c.table_owner;
select local_time into :end_time;

elapsed_time = ascii(end_time-start_time);
message :elapsed_time;
end;

execute procedure local_time_test;
\p\g

OK, there's no time required for parsing/optimizing, but at
sub-millisecond resolution it should still take some mesurable time to
run the query.

--
Roy

UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.

Ingres Forums

unread,

Mar 27, 2013, 7:56:01 AM3/27/13

to

I changed the query to be a 3 way cart prod so it took plenty of time
and it still came out as 0. current_time did the same.

Same on 10.0.1

Jeremy

--
jruffer
------------------------------------------------------------------------
jruffer's Profile: http://community.actian.com/forum/member.php?userid=504
View this thread: http://community.actian.com/forum/showthread.php?t=15126

Karl Schendel

unread,

Mar 27, 2013, 8:40:09 AM3/27/13

to Ingres and related product discussion forum

On Mar 27, 2013, at 6:57 AM, Roy Hann wrote:

> In Ingres II 10.1.0 (int.w32/105) at least, and maybe others, it looks
> like local_time gets evaluated just once in a DBP. Is that intentional?

I bet it's being evaluated at compile time. :-( Which would be a bug.

Karl

Roy Hann

unread,

Mar 27, 2013, 9:03:52 AM3/27/13

to

No, it is definitely evaluated at run time. But only once it seems.

Leandro Pinto Fava

unread,

Mar 27, 2013, 9:22:28 AM3/27/13

to info-...@kettleriverconsulting.com

> In Ingres II 10.1.0 (int.w32/105) at least, and maybe others, it looks like
> local_time gets evaluated just once in a DBP.
>

I changed the DBP to use a WHILE loop and run it over Ingres 10.0.1 and 10.1.0, in both had the same result.

1> create procedure local_time_test as

declare
start_time time(9) not null;
end_time time(9) not null;
elapsed_time varchar(80) not null;

e_time varchar(80) not null;
s_time varchar(80) not null;
i integer not null;

begin
select local_time into :start_time;

while i < 10000000 do
i = i+1;
endwhile;

select local_time into :end_time;
elapsed_time = ascii(end_time-start_time);

e_time = ascii(end_time);
s_time = ascii(start_time);
message :s_time;
message :e_time;
message :elapsed_time;
end

2> execute procedure local_time_test

MESSAGE 0: 10:12:51.312299000
MESSAGE 0: 10:12:51.312299000
MESSAGE 0: 0 00:00:00.000000000

End of Request

Leandro Fava
Setor de Informática - UNISC
Universidade de Santa Cruz do Sul
+55 51 3717 7636

Ian Kirkham

unread,

Mar 27, 2013, 11:00:48 AM3/27/13

to Ingres and related product discussion forum

It's executed at runtime but is still a bug. The execution of the
procedure is being treaded by ADF as one statement and hence it evaluate
the value and doesn't do it again.
This is a known bug that's been around a long time. There needs to be a
reset of the session query constants at the end of each statement when
in a dbp.
Regards,
Ian

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Karl
Schendel
Sent: 27 March 2013 12:40
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Evaluation of local_time in DBPs

On Mar 27, 2013, at 6:57 AM, Roy Hann wrote:

> In Ingres II 10.1.0 (int.w32/105) at least, and maybe others, it looks

> like local_time gets evaluated just once in a DBP. Is that
intentional?

I bet it's being evaluated at compile time. :-( Which would be a bug.

Karl

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

Ingres Forums

unread,

May 28, 2013, 7:47:57 PM5/28/13

to

Thanks. I think I understand now.

' tributes,memories,honour the dead,remembering the dead'
(http://www.tributes.co.ke)
'create a tribute page'
(http://tributes.co.ke/create-a-tribute-page/registers)

--
don87
------------------------------------------------------------------------
don87's Profile: http://community.actian.com/forum/member.php?userid=118546

Ingres Forums

unread,

May 29, 2013, 3:20:21 AM5/29/13

to

We saw a very similar problem last year, involving date('now') rather
than localtime. Something to do with constant folding IIRC. It was
quickly resolved with a patch.
(Bug 126861, KB doc 421968).
GJ

--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763