[Bug]: Insert with gorm failed

[DanielZhangQD]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

1.1-dev

Commit ID

0801f6b

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Insert records with gorm and failed with the following error:

2024/02/02 13:47:08.866122 +0000 ERROR gorm@v1.25.5/finisher_api.go:647 trace {"error": "Error 20301 (HY000): invalid input: invalid datetime value f6fd302d_0f87_4939_b4e4_005b53eb0627\nPrevious DML conflicts with existing constraints or data format. This transaction has to be aborted", "elapsed": "1.199696ms", "rows": 0, "sql": "INSERT INTO `mo_cloud`.`cu` (`account`,`start_time`,`end_time`,`cu`,`type`) VALUES ('f6fd302d_0f87_4939_b4e4_005b53eb0627','2024-02-02 12:57:00','2024-02-02 12:58:00','94179.7209762362274886','external_sql'),('80ed5689_82b7_48dc_bfc6_d203b126929c','2024-02-02 12:57:00','2024-02-02 12:58:00','44047.0118670933690978','external_sql')"}

MySQL [mo_cloud]> desc cu;
+------------+----------------+------+------+---------+-------+---------+
| Field      | Type           | Null | Key  | Default | Extra | Comment |
+------------+----------------+------+------+---------+-------+---------+
| account    | VARCHAR(128)   | NO   | PRI  | NULL    |       |         |
| start_time | DATETIME(3)    | NO   | PRI  | NULL    |       |         |
| end_time   | DATETIME(3)    | NO   |      | NULL    |       |         |
| cu         | DECIMAL128(23) | NO   |      | NULL    |       |         |
| type       | VARCHAR(191)   | NO   | PRI  | NULL    |       |         |
+------------+----------------+------+------+---------+-------+---------+
5 rows in set (0.10 sec)

MySQL [mo_cloud]> show create table cu;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cu    | CREATE TABLE `cu` (
`account` VARCHAR(128) NOT NULL,
`start_time` DATETIME NOT NULL,
`end_time` DATETIME NOT NULL,
`cu` DECIMAL(23,3) NOT NULL,
`type` VARCHAR(191) NOT NULL,
PRIMARY KEY (`account`,`start_time`,`type`)
) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Expected Behavior

Inserting succeeds

Steps to Reproduce

See detail in Actual Behavior

Additional information

It succeeds in mysql cli

[sukki37]

The PR that caused the issue has been reverted, so the severity has been downgraded to S0.

[jensenojs]

没进展, 但是跟pkfilter的表达式本身有bug应该是有关系的

[jensenojs]

在相关pr修复了pkfilter已有的bug之后, 需要再验证一下
#14681

[jensenojs]

在main上利用猫猫姐提供的脚本, 对比上述pr引入前后的反应, 验证问题是否修复.

• fix pkfilter bug and clean some code #14681

之前 : sys试图被插入到datatime的列中.

之后: 插入没有报错, 不过不确定account那一列是否符合预期.

后续需要将相关代码cherry-pick到1.1-dev中.

QA 复现步骤:

1. 相关SQL

create database mo_cloud; use mo_cloud;

CREATE TABLE `cu` (
`account` VARCHAR(128) NOT NULL,
`start_time` DATETIME NOT NULL,
`end_time` DATETIME NOT NULL,
`cu` DECIMAL(23,3) NOT NULL,
`type` VARCHAR(191) NOT NULL,
PRIMARY KEY (`account`,`start_time`,`type`)
);

-- 在运行脚本之前要先插入两行数据, 这**个时间可能要修改**(?).
INSERT INTO `mo_cloud`.`cu` (`account`,`start_time`,`end_time`,`cu`,`type`) VALUES 
('sys','2024-02-02 14:50:00','2024-02-02 14:51:00','15409630251032.4612','internal_sql'),
('sys','2024-02-02 14:50:00','2024-02-02 14:51:00','671823796615373.5254','external_sql');

2. 运行猫猫姐提供的脚本, 运行方式为go run cu.go statement_info.go result.go, 其中链接串和时间也需要修改一下.

cu.zip

但是对于INSERT SQL和脚本中修改的时间需要满足什么约束我其实没有太搞清楚, 好像是脚本中start-end中的时间间隔不能覆盖INSERT SQL插入的时间, 不然会违背主键去重的约束, 需要猫猫姐再确认一下

[jensenojs]

main上已经修复了, 1.1-dev上还需要cherry-pick

[jensenojs]

由于发现这个issue的问题的pr在1.1-dev上已经被revert了, (而main上没有revert, 因此可以使用猫猫姐的脚本来复现并验证是否修复), 因此换一种更简单的方式进行校验, 本质上是一样的.

create table t(a int, b varchar, c varchar, primary key(c, a));
explain insert into t (b, c, a) values("b", "c", 1);

previous

now

[Ariznawlll]

commit：194fbf752161fa3977057106837657e113aa6981

verified.

[Ariznawlll]

commit：3d2f8b383a28930945a894401097331f20b882f4

verified!