Bug #44996 Now inside of a procedure should return time procedure was exectued
Submitted: 20 May 2009 20:58 Modified: 22 May 2009 15:31
Reporter: Thomas Johnson Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version: 5.0.36sp1-enterprise-gpl-nt-log OS:Windows (Server 2003)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: now() procedure

[20 May 2009 20:58] Thomas Johnson
In testing now() within a procedure I found it was returning date & time at time the statement inside the procedure was executed instead of teh time teh procedure was executed.  Per documentation: 
NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.) 
The last part is not true.

How to repeat:
Run the following sql:

use test;


DROP PROCEDURE IF EXISTS `test`.`mytest` $$
CREATE PROCEDURE `test`.`mytest` ()
  declare myTime1, myTime2 datetime;
  declare myTime3, myTime4 datetime;
  declare iThrow integer;

  set myTime1 = now();
  set myTime3 = sysdate();

  set iThrow = sleep(2);

  set myTime2 = now();
  set myTime4 = sysdate();

  select myTime1, myTime2, myTime3, myTime4;
END $$


call mytest();

myTime1 & myTime2 should be the same with myTime3 and myTime4 being two seconds apart.  myTime1 & myTime2 ends up being two seocnds apart.

Suggested fix:
Set the timestamp option at SP execution time (and undo afterwards) so NOW() will return that time if the --sysdate-is-now isn't set.

If this is the correct behavior then the documentation need to be modified to state that NOW returns the time the statement within the procedure is exectuted not the time the SP was executed. But this may then lead (not quite sure since I haven't tested this, but gathered this from what I read) to issues with binlog recovery and replication if the timestamp isn't updated within a SP.

This may need to be fixed for Function calls too.
[22 May 2009 15:25] Paul DuBois
The documentation is incorrect. The difference between SYSDATE() and NOW() is not for stored routines and triggers, it is for stored *functions* and triggers. That means there is no effect for stored *procedures*.  Test program:

use test;


DROP FUNCTION IF EXISTS `test`.`mytest` $$
CREATE FUNCTION `test`.`mytest` ()
 set @myTime1 = now();
 set @myTime3 = sysdate();

 set @iThrow = sleep(2);

 set @myTime2 = now();
 set @myTime4 = sysdate();
return 1;

END $$


select mytest();
select @myTime1, @myTime2, @myTime3, @myTime4\G


@myTime1: 2009-05-22 10:12:56
@myTime2: 2009-05-22 10:12:56
@myTime3: 2009-05-22 10:12:56
@myTime4: 2009-05-22 10:12:58
[22 May 2009 15:29] MySQL Verification Team
Thank you for the bug report.
[22 May 2009 15:30] Paul DuBois
Background: Bug#12480, Bug#12481
[22 May 2009 15:31] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[5 Jun 2009 18:05] Paul DuBois
See also Bug#33832.