[Compatibility]: ON DELETE referential actions inflate affected-row counts

[Ariznawlll]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

bcd5ed8

Other Environment Information

- MatrixOne was built from the commit above and started locally with etc/launch/launch.toml.
- MySQL comparison version: 8.0.45 (InnoDB).
- The client OK-packet affected-row value and SELECT ROW_COUNT() return the same value on each server.

Actual Behavior

MatrixOne includes child rows changed implicitly by ON DELETE CASCADE and
ON DELETE SET NULL in the affected-row count of the parent DELETE.
MySQL counts only the parent rows directly matched by the DELETE statement.

For one matched parent with two child rows:

Referential action	MatrixOne	MySQL 8.0.45
ON DELETE CASCADE	3	1
ON DELETE SET NULL	3	1

The difference scales with the implicit child work:

Shape	MatrixOne	MySQL 8.0.45
two parents and three children, CASCADE	5	2
two parents and three children, SET NULL	5	2
one parent, one child, one grandchild	3	1
prepared parent delete with two children	3	1

The stored data is correct in both systems: CASCADE removes the related
children and SET NULL clears their foreign keys. A parent row with no related
children reports 1 on both systems. ON UPDATE CASCADE and ON UPDATE SET NULL
also report 1 on both systems for the equivalent one-parent/two-child setup.

This difference is visible both to connectors through the OK packet and to SQL
through ROW_COUNT(), so applications that use affected rows for auditing,
optimistic workflows, or batch progress receive a larger count from MatrixOne.

Expected Behavior

For MySQL compatibility, a parent DELETE should report the number of parent
rows directly affected by that statement. Rows changed only by a foreign-key
referential action should not increase the OK-packet affected-row value or
ROW_COUNT().

Steps to Reproduce

CREATE DATABASE fk_delete_affected_rows;
USE fk_delete_affected_rows;

CREATE TABLE p_cascade(id INT PRIMARY KEY);
CREATE TABLE c_cascade(
  id INT PRIMARY KEY,
  pid INT,
  FOREIGN KEY(pid) REFERENCES p_cascade(id) ON DELETE CASCADE
);
INSERT INTO p_cascade VALUES (1),(2);
INSERT INTO c_cascade VALUES (10,1),(11,1),(20,2);

DELETE FROM p_cascade WHERE id=1;
SELECT ROW_COUNT();
-- MatrixOne: 3
-- MySQL 8.0.45: 1

CREATE TABLE p_null(id INT PRIMARY KEY);
CREATE TABLE c_null(
  id INT PRIMARY KEY,
  pid INT,
  FOREIGN KEY(pid) REFERENCES p_null(id) ON DELETE SET NULL
);
INSERT INTO p_null VALUES (1),(2);
INSERT INTO c_null VALUES (10,1),(11,1),(20,2);

DELETE FROM p_null WHERE id=1;
SELECT ROW_COUNT();
-- MatrixOne: 3
-- MySQL 8.0.45: 1

SELECT * FROM c_cascade ORDER BY id;
SELECT * FROM c_null ORDER BY id;

The same count is returned when the parent delete is executed through a
prepared statement and when it is one target of a multi-table DELETE.

Additional information

MySQL documents ROW_COUNT() as the affected-row value returned by the client
API and describes CASCADE/SET NULL as automatic child-table actions:

• https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count
• https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

The MatrixOne result is not a generic index-maintenance count: ordinary primary,
unique, secondary, and FULLTEXT index deletes report the same count as MySQL.
The difference is isolated to child changes caused by parent-side ON DELETE
referential actions.

[Ariznawlll]

补充协议层验证：这个受影响行数差异同样出现在真实 MySQL 二进制预处理协议（COM_STMT_PREPARE / COM_STMT_EXECUTE），因此 JDBC、Go MySQL 驱动等读取 OK packet affected_rows 的客户端也会接收到不同结果，并非文本客户端的显示差异。

环境：main / bcd5ed833b5400fe95f322e4028adb2bde3de0a4，本地单 CN；MySQL 8.0.45 对照。每项连续执行 3 次，结果一致。

通过 Go MySQL 驱动 prepare 并执行：

DELETE FROM parent_t WHERE id = ?;

父表中删除 id=1，该父行关联两条子行时：

外键动作	MO RowsAffected()	MySQL RowsAffected()	最终数据
ON DELETE CASCADE	3	1	父行删除；两条子行删除；其余父/子行一致
ON DELETE SET NULL	3	1	父行删除；两条子行的 FK 置空；其余数据一致

边界对照：父行不存在关联子行时两端都返回 1；ON UPDATE CASCADE 更新一条父行时两端也都返回 1。说明差异只出现在 DELETE 引发的隐式 referential action 计数，数据维护本身正确。

[Ariznawlll]

补充 REPLACE 语法的同类影响：该 affected-row 差异不限于显式 DELETE。

环境：main / bcd5ed833b5400fe95f322e4028adb2bde3de0a4，本地单 CN；MySQL 8.0.45 对照；Go MySQL 驱动的二进制预处理协议。连续 3 次一致。

前置数据为父表 replace_p(id PRIMARY KEY, code UNIQUE) 与子表 replace_c(pid REFERENCES replace_p(id) ON DELETE CASCADE)；id=1 的父行有一条子行。通过 prepare 后执行：

REPLACE INTO replace_p(id, code) VALUES (?, ?);

绑定 (1, 99) 后，两端最终数据一致：旧父行被替换为 (1,99)，关联子行被级联删除，其他父/子行保持不变。但 MO 的 RowsAffected() 是 3（父行删除 + 子行级联删除 + 新父行插入），MySQL 是 2（被替换父行删除 + 新父行插入）。

ON DELETE RESTRICT 对照也已执行：两端都拒绝该 REPLACE，父表与子表均保持原样。因此这里仍是受影响行数的协议语义差异，不是 REPLACE 的级联数据维护错误。