[Info-Ingres] dates and procedures in 10.1

[Martin Bowes]

Hi All,

 

On II 10.1.0 (a64.lnx/126)NPTL + p14302

 

The date(‘now’) in a procedure fixes on the date the procedure was created and not the actual date(‘now’) when it is executed.

 

Try this:

create procedure test_date as declare msg varchar(256) not null not default; begin msg = 'date is ' + varchar(date('now')); message :msg;end;

 

execute procedure test_date\t\p\g

Thu Aug  2 15:42:37 2012

execute procedure test_date

Executing . . .

 

MESSAGE 0: date is 02/08/2012 15:39:25

 

And again…

Thu Aug  2 15:43:20 2012

Executing . . .

 

MESSAGE 0: date is 02/08/2012 15:39:25

 

Maximum suckage!

 

Martin Bowes

[Karl Schendel]

On Aug 2, 2012, at 10:43 AM, Martin Bowes wrote:

> Hi All,
>
> On II 10.1.0 (a64.lnx/126)NPTL + p14302
>
> The date(‘now’) in a procedure fixes on the date the procedure was created and not the actual date(‘now’) when it is executed.
>

Oops. Constant folding strikes! I'm guessing that we have one of the
function definition flags set a bit astray, allowing folding when it should
not be allowed.

If you don't already have an issue open on this, you might want to open
one for bug tracking purposes.

Karl

[Martin Crouch]

Perhaps try and trick it?

create procedure test_date as
declare msg varchar(256) not null not default;

declare tmp varchar(5) not null not default;
begin
select 'now' into :tmp;
msg = 'date is ' + varchar(date(:tmp));
message :msg;
end;

[Ingres Forums]

Yep, thanks Martin that's a nice workaround.
GJ


--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763
View this thread: http://community.actian.com/forum/showthread.php?t=14608

[Martin Bowes]

Hi Karl,

Yes we opened an issue at severity two. Amongst other effects Its crippled a lot of our RFPs that use date('now') for recording the time things are done.

I said many naughty words out loud when we saw this.

Marty

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

[Ingres Forums]

Weird... I get an error when executing that statement (on 10.0.0
Linux/64bit)


Code:
--------------------

[ingres@thost ~]$ sql dejandb
INGRES TERMINAL MONITOR Copyright 2010 Ingres Corporation
Ingres Linux Version II 10.0.0 (a64.lnx/125)NPTL login
Fri Aug 3 09:17:08 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* create procedure test_date as
* declare msg varchar(256) not null not default;
* declare tmp varchar(5) not null not default;
* begin
* select 'now' into :tmp;
* msg = 'date is ' + varchar(date(:tmp));
* message :msg;
* end;
* \g
Executing . . .

E_US0962 Invalid column format 'tmp varchar'.
(Fri Aug 3 09:17:14 2012)

E_AD2003 ADF routine was passed an unknown datatype name.
(Fri Aug 3 09:17:14 2012)

--------------------


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

[Ingres Forums]

> Weird... I get an error when executing that statement (on 10.0.0
Linux/64bit)

Remove the declare before tmp.

GJ


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

[Ingres Forums]

Just delete the second occurrence of *declare*; it shouldn't be there.


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

[Martin Bowes]

Hi Dejan,

The procedure as written has a slight bug in using two declare statements.

It should read...
create procedure test_date as

declare
msg varchar(256) not null not default;

tmp varchar(5) not null not default;

begin
tmp = 'now';

msg = 'date is ' + varchar(date(:tmp));

message :msg;
end;

Marty

-----Original Message-----
From: Ingres Forums [mailto:info-...@kettleriverconsulting.com]
Sent: 03 August 2012 09:28
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] dates and procedures in 10.1

[Martin Crouch]

On 03/08/2012 09:30, Ingres Forums wrote:
> Just delete the second occurrence of *declare*; it shouldn't be there.
>
>

Sorry I didn't try my code before posting (don't have a Ingres 10.1
installation). But the basic idea was to use a variable sourced from
the database (containing now) rather than a constant. This means that
it shouldn't get optimised and has to be calculated at run time.

Even if the above works the original bug should be fixed...

[Martin Bowes]

Definitely getting it fixed!

This is one of the worst kind of bugs. You would never test for it. Furthermore when I first became aware of it I assumed it was some weird artefact of the upgrade and simply reloaded the existing procedure. Of course we then tested it straight after a reload and look the date/times are OK...it never occurred to me to wait ten seconds and have another try.

Kicking myself on that one!

Marty

-----Original Message-----
From: Martin Crouch [mailto:spamsp...@spaml.com]
Sent: 03 August 2012 10:39
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] dates and procedures in 10.1