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: Per-Erik Martin 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.