MySQL Bugs: #111488: Timestamp column used in generated columns gets updated without changing it

Bug #111488	Timestamp column used in generated columns gets updated without changing it
Submitted:	19 Jun 2023 15:23	Modified:	20 Jun 2023 12:06
Reporter:	Hossam Maurice	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.38	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When we update a record in a table schema that has a timestamp column used as a generated column in another column.

The timestamp column value will be updated with the value of the current time even if this column wasn't set in the update query.

But if we set the value of the timestamp column in the update query, the data will be reflected normally in the database.

This issue doesn't exist in version 8.0.

How to repeat:
CREATE TABLE `items` (
  `id` varchar(36) PRIMARY KEY,
  `timestamp` timestamp NOT NULL,
  `note` text,
  `hour_of_day` int unsigned GENERATED ALWAYS AS (date_format(`timestamp`,_utf8mb4'%H')) STORED,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
);

INSERT INTO items (`id`, `timestamp`, `note`, `created_at`, `updated_at`) VALUES ("id-1", "2022-01-12 10:05:30", "test note", "2022-01-20 10:05:30", "2022-01-22 10:05:30");

INSERT INTO items (`id`, `timestamp`, `note`, `created_at`, `updated_at`) VALUES ("id-2", "2022-01-20 10:05:30", "test note 2", "2022-01-21 10:05:30", "2022-01-23 10:05:30");

UPDATE items SET `note` = "updated test note" WHERE `id` = "id-1"; 

SELECT * FROM items WHERE `id` = "id-1";

Suggested fix:
The "select" query will return a `timestamp` column with the value of the current time. While it should return "2022-01-12 10:05:30" since it wasn't updated.

This issue doesn't exist in version 8.0.

Hi Mr. Maurice,

Thank you for your bug report.

We have tested your test case with latest 8.0 and a bug is not present there any more.

Since 5.7 is in the maintenance mode, it receives only fixes for the crashing bugs. This bug is not a crashing nor security bug, so we recommend you to upgrade to 8.0. 8.0 will not be getting any new features, but it will receive bug fixes for the longer period of time.

Unsupported.