MySQL Bugs: #44996: Now inside of a procedure should return time procedure was exectued

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:	None
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

Description:
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;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`mytest` $$
CREATE PROCEDURE `test`.`mytest` ()
BEGIN
  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 $$

DELIMITER ;

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.

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;

DELIMITER $$

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

 set @iThrow = sleep(2);

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

END $$

DELIMITER ;

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

Result:

@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

Thank you for the bug report.

Background: Bug#12480, Bug#12481

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.

See also Bug#33832.