Invalid keys may get locked

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Execute the following SQL, then query the MVCC of an invalid key 7480000000000000005F728000000000000000.

/* test */ drop table if exists t1, t2;
/* test */ create table t1  (c_int int, c_str varchar(40), primary key (c_int, c_str) , key(c_int)) partition by hash (c_int) partitions 4;
/* test */ create table t2  like t1 ;
/* test */ insert into t1 values (1, 'flamboyant mcclintock');
/* test */ insert into t2 select * from t1 ;
/* test */ begin;
/* test */ insert into t2 (c_int, c_str) values (2, 'romantic grothendieck');
/* test */ select * from t2 left join t1 on t1.c_int = t2.c_int for update;
/* test */ commit;

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

The MVCC info should be empty.

3. What did you see instead (Required)

The key was locked unexpectedly. It seems the partition id column (used as the physical id) is actually null (thus row.GetInt64(offset) returns 0) in this case.

4. What is your TiDB version? (Required)

master

[cfzjywxk]

From the debug log added in #28060.

[executor.go:957] ["[for debug] the physicalID or handle value is unexpected"] [conn=875] [ts=427743752351907842] [id=4568] [physicalID=0] [handle=10] [sql="/* tp-test:q:1631712899.f924863c-d083-4c81-913d-4b4618deffb1:69 */ select * from t1 join t2 on t1.c_int = t2.c_int and t1.c_enum <= t2.c_enum for update"] [tblIDs="[4568,4487]"] [partTblLen=2] [tblID2PIDColIdxMapKeys="[4487,4568]"] [tblid2ColIdxMapVals="[8,16]"]

Seems the calculation for the physical id is unexpected, possibly the tblID2PIDColumnIndex is set incorrectly.

@tiancaiamao
PTAL

[tiancaiamao]

Caused by #26373 and related to #28292

When partition table need to handle lock(write the lock key), it add an extra partition ID column to the chunk.
The bug is, the write operation does not recognize the extra partition ID column and write it back to the TiKV.

[mjonss]

/component tablepartition

[mjonss]

Fixed by #31634.

[zyguan]

Here is an another case, @mjonss @tiancaiamao PTAL.

drop table if exists t1, t2;
create table t1  (c_int int, c_str varchar(40), primary key (c_int, c_str));
create table t2  (c_int int, c_str varchar(40), primary key (c_int)) partition by hash (c_int) partitions 4;
insert into t1 (`c_int`, `c_str`) values (1, 'upbeat solomon'), (5, 'sharp rubin');
insert into t2 (`c_int`, `c_str`) values (1, 'clever haibt'), (4, 'kind margulis');
begin;
select * from t1 left join t2 on t1.c_int = t2.c_int for update;
-- curl http://$tidb_host:10080/mvcc/hex/7480000000000000005F728000000000000000

[tiancaiamao]

I find the root cause and will file a PR to fix.

select * from t1 left join t2 on t1.c_int = t2.c_int for update;

The join right side is NULL, added by the left join.
So there is no physical ID column... and we get the physical ID as 0, and construct the wrong key.