Bug #6857 current_time() in STORED PROCEDURES
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;
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.
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;