Errors Occur in Some DML Executions After Using Virtual Columns

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 使用虚拟列后某些DML执行报错

| username: Miracle

[TiDB Version] TiDB V6.5.9, V7.5.1 both have this issue,

[Impact of the Bug] DML statements cannot be executed

[Possible Steps to Reproduce the Issue]
SQL:

CREATE TABLE test1 (
    col1 bigint(20) NOT NULL,
    col2 varchar(36) NOT NULL,
    col3 int(11) DEFAULT NULL,
    col4 varchar(36) NOT NULL,
    col5 varchar(255) DEFAULT NULL,
    modify_time bigint(20) DEFAULT NULL,
    create_time bigint(20) DEFAULT NULL,
    col6 json DEFAULT NULL,
    col7 json DEFAULT NULL,
    col8 json GENERATED ALWAYS AS (json_merge_patch(ifnull(col6, _utf8mb4'{}'), ifnull(col7, _utf8mb4'{}'))) STORED,
    col9 varchar(36) GENERATED ALWAYS AS (left(json_unquote(json_extract(col8, _utf8mb4'$.col9[0]')), 36)) VIRTUAL,
    col10 varchar(30) GENERATED ALWAYS AS (left(json_unquote(json_extract(col8, _utf8mb4'$.col10')), 30)) VIRTUAL,
    KEY dev_idx1 (col4, col1, col10, col3, col5),
    KEY dev_idx2 (col3, col1, col9)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO test1 VALUES (-100000000, '123459789332', 1, '123459789332', 'AAAAA', 1675871871, 1675760995, '{"col10": "CCCCC", "col9": ["ABCDEFG"]}', '{"col10": "DDDDD", "col9": ["abcdefg"]}', DEFAULT, DEFAULT, DEFAULT);

INSERT INTO test1 VALUES (-100000000, '123459789332', 1, '123459789332', 'BBBBB', 1675908861, 1675908861, '{"col10": "CCCCC", "col9": ["ABCDEFG"]}', NULL, DEFAULT, DEFAULT, DEFAULT);

INSERT INTO test1 VALUES (0, '123459789332', 1, '123459789332', 'AAAAA', 1675956409, 1675908882, '{"col10": "CCCCC", "col9": ["ABCDEFG"]}', '{"col10": "DDDDD", "col9": ["abcdefg"]}', DEFAULT, DEFAULT, DEFAULT);

INSERT INTO test1 VALUES (-100000000, '123459789332', 1, '123459789332', 'BBBBB', 1675871896, 1675871896, '{"col10": "CCCCC", "col9": ["ABCDEFG"]}', NULL, DEFAULT, DEFAULT, DEFAULT);

INSERT INTO test1 VALUES (-100000000, '123459789332', 1, '123459789332', 'AAAAA', 1675908836, 1675871916, '{"col10": "CCCCC", "col9": ["ABCDEFG"]}', '{"col10": "DDDDD", "col9": ["abcdefg"]}', DEFAULT, DEFAULT, DEFAULT);

UPDATE test1 SET col7 = '{"col10":"DDDDD","col9":["abcdefg"]}' WHERE col2 = '123459789332';

UPDATE test1 SET col1 = -100000000 WHERE col4 = '123459789332';

DELETE FROM test1 WHERE col1 < 0;

[Observed Unexpected Behavior] DELETE reports an error

[Expected Behavior] DELETE should execute normally

[Other Background Information or Screenshots]

Enterprise WeChat Screenshot_17181044819480
Enterprise WeChat Screenshot_171810448194801912×828 35.1 KB

Enterprise WeChat Screenshot_17181091394749
Enterprise WeChat Screenshot_171810913947491912×874 35.9 KB

PS: In version V5.4.0, the above SQL can be executed, but it will cause a bug where data and indexes are inconsistent.

image
image1912×872 33.1 KB

2
| username: ziptoam | Original post link
  1. Generated Column Dependencies and Update Conflicts: Since col8, col9, and col10 are generated columns, with col8 being calculated based on col6 and col7, and col9 and col10 depending on col8, any issues in the calculation logic of the generated columns (such as data inconsistency or type mismatch) caused by a previous operation (like an UPDATE) before the DELETE operation could indirectly affect the DELETE operation.
  2. Index Conflicts: The table has two indexes, dev_idx1 and dev_idx2. When the DELETE operation triggers updates to these indexes, if the values of the generated columns cause uniqueness conflicts or other constraint violations in the indexes, it could also lead to DELETE failure.
3
| username: Billmay表妹 | Original post link

I opened an issue at dml on table contains generated columns report assertion failed error · Issue #53967 · pingcap/tidb · GitHub, and it can also be reproduced on the latest master. You can see the specific progress in the issue.