tidb_shard index on ON UPDATE CURRENT_TIMESTAMP column causes ERROR 8141 (HY000): assertion failed

[zaakn]

Bug Report

As the title says.

1. Minimal reproduce step (Required)

CREATE TABLE `item` (
	`id` int NOT NULL AUTO_INCREMENT,
	`status` tinyint(1) NOT NULL,
	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,	
	PRIMARY KEY (`id`),
	KEY ((tidb_shard(`updated_at`)), `updated_at`)
);

mysql> INSERT INTO item(status) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM item;
+----+--------+---------------------+
| id | status | updated_at          |
+----+--------+---------------------+
|  1 |      1 | 2024-07-22 13:20:55 |
+----+--------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE item SET status = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM item;
+----+--------+---------------------+
| id | status | updated_at          |
+----+--------+---------------------+
|  1 |      2 | 2024-07-22 13:21:28 |
+----+--------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE item SET status = 3;
ERROR 8141 (HY000): assertion failed: key: 748000000000000a925f69800000000000000104000000000000008f0419b3ecd55c000000038000000000000001, assertion: Exist, start_ts: 451321398243360772, existing start ts: 0, existing commit ts: 0

2. What did you expect to see? (Required)

no assertion failed.

3. What did you see instead (Required)

--

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[zaakn]

Possibly related issues:

• ON UPDATE CURRENT_TIMESTAMP may lead to data inconsistency #44565

[you06]

Root cause: the ON UPDATE CURRENT_TIMESTAMP is handled after generated column (a hidden column created by expression index) is calculated, thus the updated_at column is not consistent with the generated column.

• The INSERT INTO ... ON DUPLICATE KEY UPDATE statement has the same issue.

CREATE TABLE item (
	id int NOT NULL AUTO_INCREMENT,
	status tinyint(1) NOT NULL,
	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	KEY ((tidb_shard(updated_at)), updated_at)
);

INSERT INTO item(id, status) VALUES(1, 1);
SELECT SLEEP(1);
INSERT INTO item(id, status) VALUES (1, 2) ON DUPLICATE KEY UPDATE status=VALUES(status); -- the data is in consistency after this SQL
SELECT SLEEP(1);
INSERT INTO item(id, status) VALUES (1, 3) ON DUPLICATE KEY UPDATE status=VALUES(status);
ADMIN CHECK TABLE item; -- ERROR 1054 (42S22): Unknown column '_V$_expression_index_0' in 'field list'

• Other expression index functions have the same issue, e.g. replace tidb_shard with md5.

[zaakn]

I just found:
Not only UPDATE / INSERT ... ON DUPLICATE KEY UPDATE, but also DELETE will cause assertion failure.

DELETE FROM item WHERE id = 1; -- it's the same without WHERE
-- ERROR 8141 (HY000): assertion failed: key: 748000000000000a925f69800000000000000104000000000000008f0419b3ecd55c000000038000000000000001, assertion: Exist, start_ts: 451821329421959169, existing start ts: 0, existing commit ts: 0

@you06