MySQL Bugs: #99513: "ALTER TABLE ... ADD ... DEFAULT now()" generates wrong time at first INSERT

Bug #99513	"ALTER TABLE ... ADD ... DEFAULT now()" generates wrong time at first INSERT
Submitted:	11 May 2020 13:21	Modified:	8 Jun 2020 13:22
Reporter:	Wim Goedertier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The first INSERT after
"ALTER TABLE ... ADD/MODIFY ... DATETIME DEFAULT now();"
stores an incorrect time.
(It stores the time of the ALTER TABLE instead of the time of the INSERT.)
Subsequent INSERTs store the correct time.

The first INSERT after
"CREATE TABLE ... ( ..., ... DATETIME DEFAULT now() );"
behaves correct.

If you create a column with default now() using CREATE TABLE
and a 2nd column using ALTER TABLE ... ADD/MODIFY,
then both columns behave incorrect.
(It looks like the ALTER TABLE contaminates the column generate with CREATE TABLE.)

How to repeat:
Logs (times of execution manualy added):

@14:36:09> DROP TABLE IF EXISTS tst1;
@14:36:09> CREATE TABLE tst1 ( id DECIMAL(3) );
@14:36:14> ALTER TABLE tst1 ADD date1 DATETIME DEFAULT (now());
@14:36:19> INSERT INTO tst1 (id) VALUES (1);
@14:36:24> SELECT * FROM tst1;
+------+---------------------+
| id   | date1               |
+------+---------------------+
|    1 | 2020-05-11 14:36:14 |
+------+---------------------+
@14:36:24> DESCRIBE tst1;
+-------+--------------+------+-----+---------+-------------------+
| Field | Type         | Null | Key | Default | Extra             |
+-------+--------------+------+-----+---------+-------------------+
| id    | decimal(3,0) | YES  |     | NULL    |                   |
| date1 | datetime     | YES  |     | now()   | DEFAULT_GENERATED |
+-------+--------------+------+-----+---------+-------------------+

@14:37:05> DROP TABLE IF EXISTS tst1;
@14:37:10> CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (now()) );
@14:37:15> INSERT INTO tst1 (id) VALUES (1);
@14:37:20> SELECT * FROM tst1;
+------+---------------------+
| id   | date1               |
+------+---------------------+
|    1 | 2020-05-11 14:37:15 |
+------+---------------------+
@14:36:24> DESCRIBE tst1;
+-------+--------------+------+-----+---------+-------------------+
| Field | Type         | Null | Key | Default | Extra             |
+-------+--------------+------+-----+---------+-------------------+
| id    | decimal(3,0) | YES  |     | NULL    |                   |
| date1 | datetime     | YES  |     | now()   | DEFAULT_GENERATED |
+-------+--------------+------+-----+---------+-------------------+

@15:10:25> DROP TABLE IF EXISTS tst1;
@15:10:25> CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (now()) );
@15:10:30> ALTER TABLE tst1 ADD COLUMN date2 DATETIME DEFAULT (now());
@15:10:35> INSERT INTO tst1 (id) VALUES (1);
@15:10:40> SELECT * FROM tst1;
+------+---------------------+---------------------+
| id   | date1               | date2               |
+------+---------------------+---------------------+
|    1 | 2020-05-11 15:10:30 | 2020-05-11 15:10:30 |
+------+---------------------+---------------------+
@15:10:40> DESCRIBE tst1;
+-------+--------------+------+-----+---------+-------------------+
| Field | Type         | Null | Key | Default | Extra             |
+-------+--------------+------+-----+---------+-------------------+
| id    | decimal(3,0) | YES  |     | NULL    |                   |
| date1 | datetime     | YES  |     | now()   | DEFAULT_GENERATED |
| date2 | datetime     | YES  |     | now()   | DEFAULT_GENERATED |
+-------+--------------+------+-----+---------+-------------------+

Script to test:

mysql -v -t -h 127.0.0.1 -u root --password=root tst << EOF

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3) );
ALTER TABLE tst1 ADD date1 DATETIME DEFAULT (now());
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (now()) );
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (now()) );
ALTER TABLE tst1 ADD date2 DATETIME DEFAULT (now());
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

EOF

If I replace now() by sysdate()
(and ADD by MODIFY to circumvent bug #99512)
the problem doesn't pop up.

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (sysdate()) );
ALTER TABLE tst1 MODIFY date1 DATETIME DEFAULT (sysdate());
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (sysdate()) );
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

DROP TABLE IF EXISTS tst1;
CREATE TABLE tst1 ( id DECIMAL(3), date1 DATETIME DEFAULT (sysdate()), date2 DATETIME DEFAULT (sysdate()) );
ALTER TABLE tst1 MODIFY date2 DATETIME DEFAULT (sysdate());
SELECT sysdate(), SLEEP(5), sysdate();
INSERT INTO tst1 (id) VALUES (1);
SELECT * FROM tst1;
DESCRIBE tst1;

Hi Mr. Goedertier,

Thank you for your bug report.

I have managed to repeat it with 8.0.20, by introducing one minute of sleep between ALTER and INSERT:

now()
2020-05-11 17:03:40
Field	Type	Null	Key	Default	Extra
id	decimal(3,0)	YES		NULL
mydate	datetime	YES		now()	DEFAULT_GENERATED
now()
2020-05-11 17:03:40
sleep(60)
0
now()
2020-05-11 17:04:40
id	mydate
1	2020-05-11 17:03:40

Verified as reported.

Posted by developer:
 
Fixed in 8.0.22.

After ALTER TABLE to add an expression default to a column, the first
insert inserted a value as if the expression had been evaluated at
alter time and not insert time.

Thank you, Paul.