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;