Repository navigation
Exchange partition creates duplicate _tidb_rowid causing UPDATE to skip rows in nonclustered PK tables #65067
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
-- Create tables with nonclustered primary key
DROP TABLE IF EXISTS t, tp;
CREATE TABLE t (a INT, b INT, dt DATE, PRIMARY KEY (a) NONCLUSTERED);
CREATE TABLE tp (a INT, b INT, dt DATE, PRIMARY KEY (a) NONCLUSTERED)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (20)
);
-- Insert test data
INSERT INTO tp(a,b) VALUES (2,2),(4,4),(6,6);
INSERT INTO t(a,b) VALUES (12,12),(14,14),(15,15),(16,16);
-- Check _tidb_rowid before exchange
SELECT *, _tidb_rowid FROM t ORDER BY a;
-- Returns: a=12,14,15,16 with _tidb_rowid=1,2,3,4
SELECT *, _tidb_rowid FROM tp ORDER BY a;
-- Returns: a=2,4,6 with _tidb_rowid=1,2,3
-- Exchange partition
ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t;
-- Verify _tidb_rowid conflicts exist
SELECT *, _tidb_rowid FROM tp ORDER BY a;
-- Shows duplicate _tidb_rowid values: (a=2,12 both have rowid=1), (a=4,14 both have rowid=2), (a=6,15 both have rowid=3)
-- Attempt to update all rows
UPDATE tp SET dt = NOW();
-- Expected: 7 rows affected
-- Actual: Only 4 rows affected
SELECT *, _tidb_rowid FROM tp ORDER BY a;
-- Shows only a=12,14,15,16 were updated (rows from exchanged partition)
-- Rows a=2,4,6 (from original partitions) were NOT updated
-- Another update to confirm the issue
UPDATE tp SET b = 333;
-- Expected: 7 rows affected
-- Actual: Only 4 rows affected
SELECT *, _tidb_rowid FROM tp ORDER BY a;
-- Shows only a=2,4,6,16 were updated
-- Rows a=12,14,15 were NOT updated2. What did you expect to see? (Required)
After EXCHANGE PARTITION, all rows in table tp should have unique _tidb_rowid values. The UPDATE statement without a WHERE clause should modify all 7 rows in the table.
Expected behavior:
UPDATE tp SET dt = NOW()should affect 7 rowsUPDATE tp SET b = 333should affect 7 rows- All rows should be successfully updated
3. What did you see instead (Required)
After EXCHANGE PARTITION, multiple rows have conflicting _tidb_rowid values:
+----+------+------+-------------+
| a | b | dt | _tidb_rowid |
+----+------+------+-------------+
| 2 | 2 | NULL | 1 | -- Conflict with a=12
| 4 | 4 | NULL | 2 | -- Conflict with a=14
| 6 | 6 | NULL | 3 | -- Conflict with a=15
| 12 | 12 | NULL | 1 | -- Conflict with a=2
| 14 | 14 | NULL | 2 | -- Conflict with a=4
| 15 | 15 | NULL | 3 | -- Conflict with a=6
| 16 | 16 | NULL | 4 | -- No conflict
+----+------+------+-------------+
When executing UPDATE without a WHERE clause, only 4 rows are updated instead of 7. For rows with duplicate _tidb_rowid values, the UPDATE appears to process only one row per _tidb_rowid.
First UPDATE result (UPDATE tp SET dt = NOW()):
+----+------+------------+-------------+
| a | b | dt | _tidb_rowid |
+----+------+------------+-------------+
| 2 | 2 | NULL | 1 | -- NOT updated
| 4 | 4 | NULL | 2 | -- NOT updated
| 6 | 6 | NULL | 3 | -- NOT updated
| 12 | 12 | 2025-12-16 | 1 | -- Updated
| 14 | 14 | 2025-12-16 | 2 | -- Updated
| 15 | 15 | 2025-12-16 | 3 | -- Updated
| 16 | 16 | 2025-12-16 | 4 | -- Updated
+----+------+------------+-------------+
Second UPDATE result (UPDATE tp SET b = 333):
+----+------+------------+-------------+
| a | b | dt | _tidb_rowid |
+----+------+------------+-------------+
| 2 | 333 | NULL | 1 | -- Updated this time
| 4 | 333 | NULL | 2 | -- Updated this time
| 6 | 333 | NULL | 3 | -- Updated this time
| 12 | 12 | 2025-12-16 | 1 | -- NOT updated this time
| 14 | 14 | 2025-12-16 | 2 | -- NOT updated this time
| 15 | 15 | 2025-12-16 | 3 | -- NOT updated this time
| 16 | 333 | 2025-12-16 | 4 | -- Updated
+----+------+------------+-------------+
The UPDATE operation inconsistently skips rows with duplicate _tidb_rowid, causing data inconsistency.
4. What is your TiDB version? (Required)
v6.5.11
Additional Information:
This appears to be related to [Issue #64176](#64176). The EXCHANGE PARTITION operation does not properly reassign _tidb_rowid for nonclustered primary key tables, leading to duplicate rowid values across partitions. This breaks the uniqueness assumption of implicit rowid and causes UPDATE operations to produce incorrect results.
Activity
WalterWj commentedon Dec 16, 2025
Additional Information:
This issue appears similar to but is NOT the same as Issue #64176.
Key difference:
Testing on latest version:
Tested on v9.0.0-beta.2.pre-nightly - the issue persists with the same symptoms:
EXCHANGE PARTITIONcreates duplicate_tidb_rowidvaluesUPDATEwithout WHERE clause skips rows inconsistentlyThe
EXCHANGE PARTITIONoperation still does not properly reassign_tidb_rowidfor nonclustered primary key tables, leading to duplicate rowid values across partitions. This breaks the uniqueness assumption of implicit rowid and causes UPDATE operations to produce incorrect results.Workaround:
Until this is fixed, avoid using
EXCHANGE PARTITIONwith nonclustered primary key tables, or always use explicit WHERE clauses in UPDATE statements to target specific rows by primary key values.tiancaiamao commentedon Dec 16, 2025
Comment those 3 lines and the bug is gone
tidb/pkg/executor/update.go
Lines 143 to 146 in ee0a2f8
But only 4 rows are passed down, and 3 rows are filter out here.
executor: nonclustered partitioned table could miss updates due to du…
executor: nonclustered partitioned table could miss updates due to du…
executor: nonclustered partitioned table could miss updates due to du…
executor: nonclustered partitioned table could miss updates due to du…