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