Bug #6857 current_time() in STORED PROCEDURES
Submitted: 27 Nov 2004 18:13 Modified: 25 Feb 2005 17:17
Reporter: Alois Lindner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1 OS:Linux (Linux)
Assigned to: Bugs System CPU Architecture:Any

[27 Nov 2004 18:13] Alois Lindner
Description:
Problem: In a PROCEDURE a "current_time", "current_timestamp" creates a constant time value
MySQL-Version: 5.0.1-alpha-Max-log
OS: Linux

mysql> INSERT INTO CurrentTimeStampTable (A, C) VALUES (1, current_time());
Query OK, 1 row affected (0.00 sec)

mysql> CALL CurrentTimeStampTest(2, 1000000);
INSERT INTO CurrentTimeStampTable (A, C) VALUES (10, current_time());
CALL CurrentTimeStampTest(11, 1000000);

SELECT * FROM CurrentTimeStampTable ORDER BY A;
Query OK, 1 row affected (10.06 sec)

mysql> INSERT INTO CurrentTimeStampTable (A, C) VALUES (10, current_time());
Query OK, 1 row affected (0.03 sec)

mysql> CALL CurrentTimeStampTest(11, 1000000);
Query OK, 1 row affected (10.05 sec)

mysql>
mysql> SELECT * FROM CurrentTimeStampTable ORDER BY A;
+------+---------------------+----------+
| A    | B                   | C        |
+------+---------------------+----------+
|    1 | 2004-11-27 20:06:52 | 20:06:52 |
|    2 | 2004-11-27 20:06:52 | 20:06:52 | ---> same times, but the
|    3 | 2004-11-27 20:06:52 | 20:06:52 | ---> CALL takes about 10 sec
|   10 | 2004-11-27 20:07:02 | 20:07:02 |
|   11 | 2004-11-27 20:07:02 | 20:07:02 | ---> same times, but the
|   12 | 2004-11-27 20:07:02 | 20:07:02 | ---> CALL takes about 10 sec
+------+---------------------+----------+
6 rows in set (0.03 sec)

How to repeat:
Simple SQL-Code:

DROP TABLE IF EXISTS CurrentTimeStampTable;

CREATE TABLE CurrentTimeStampTable (
  A      INTEGER,
  B      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  C      TIME);

DROP PROCEDURE IF EXISTS CurrentTimeStampTest;

delimiter //

CREATE PROCEDURE CurrentTimeStampTest (iStart INTEGER, iWait INTEGER)
BEGIN
  DECLARE icounter         INTEGER DEFAULT 0;
  DECLARE iTime            TIME;

  SET iTime = current_time();
  INSERT INTO CurrentTimeStampTable (A, C) VALUES (iStart, iTime);

  -- do nothing, only time consuming
  REPEAT
    SET icounter = ROUND(icounter + 1, 0);
  UNTIL icounter >= iWait
  END REPEAT;

  SET iTime = current_time();
  INSERT INTO CurrentTimeStampTable (A, C) VALUES (iStart + 1, iTime);
END;
//

delimiter ;

INSERT INTO CurrentTimeStampTable (A, C) VALUES (1, current_time());
CALL CurrentTimeStampTest(2, 1000000);
INSERT INTO CurrentTimeStampTable (A, C) VALUES (10, current_time());
CALL CurrentTimeStampTest(11, 1000000);

SELECT * FROM CurrentTimeStampTable ORDER BY A;
[27 Nov 2004 22:54] MySQL Verification Team
Verified with latest BK source 5.0.