new collation use latin1，primary key is clustered index，index and data check are inconsistent

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set new_collations_enabled_on_first_bootstrap: true
create table t1 ( c_int int, c_str varchar(40) character set latin1 collate latin1_bin, primary key(c_int, c_str(8)) clustered , key(c_int) , key(c_str) );
insert into t1 values (1, 'jovial edison');
admin check table t1;

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

mysql> admin check table t1;
Query OK, 0 rows affected (0.06 sec)

3. What did you see instead (Required)

mysql> admin check table t1;
ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x3, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x1, 0x1, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xf8}, colEndOffsets:[]uint16{0x9, 0x13}}, index:types.Datum{k:0x1, decimal:0x0, length:0x0, i:1, collation:"", b:[]uint8(nil), x:interface {}(nil)} != record:

4. What is your TiDB version? (Required)

Release Version: v5.0.1
Edition: Community
Git Commit Hash: 1145e34
Git Branch: heads/refs/tags/v5.0.1
UTC Build Time: 2021-04-23 07:32:04
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[cfzjywxk]

@vivid392845427 Dose non-clustered index table has such problems?

[vivid392845427]

only occurs in clustered index，has been modified the issue title

[vivid392845427]

another Scenes：
create table t ( c_int int, c_str varchar(40) character set ascii collate ascii_bin, primary key(c_int, c_str(8)) clustered , unique key(c_str) );
insert into t values (3, 'ferventr brattain');
admin check table t;---check ok
insert into t values (3, 'fervent brattain');
admin check table t;---check fail
mysql> admin check table t;
ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x3, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x3, 0x1, 0x66, 0x65, 0x72, 0x76, 0x65, 0x6e, 0x74, 0x0, 0xfe}, colEndOffsets:[]uint16{0x9, 0x13}}, index:types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, collation:"ascii_bin", b:[]uint8{0x66, 0x65, 0x72, 0x76, 0x65, 0x6e, 0x74, 0x20, 0x62, 0x72, 0x61, 0x74, 0x74, 0x61, 0x69, 0x6e}, x:interface {}(nil)} != record:

[cfzjywxk]

/cc @lysu @wjhuang2016

[lysu]

another Scenes：
create table t ( c_int int, c_str varchar(40) character set ascii collate ascii_bin, primary key(c_int, c_str(8)) clustered , unique key(c_str) );
insert into t values (3, 'ferventr brattain');
admin check table t;---check ok
insert into t values (3, 'fervent brattain');
admin check table t;---check fail
mysql> admin check table t;
ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x3, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x3, 0x1, 0x66, 0x65, 0x72, 0x76, 0x65, 0x6e, 0x74, 0x0, 0xfe}, colEndOffsets:[]uint16{0x9, 0x13}}, index:types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, collation:"ascii_bin", b:[]uint8{0x66, 0x65, 0x72, 0x76, 0x65, 0x6e, 0x74, 0x20, 0x62, 0x72, 0x61, 0x74, 0x74, 0x61, 0x69, 0x6e}, x:interface {}(nil)} != record:

has be addressed with #24578

but the issue still have problem if run with tikv, it can be simplified as

mysql> create table t1 ( c_int int, c_str varchar(40) character set latin1 collate latin1_bin, primary key(c_str) clustered , key(c_int));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values (1, 'jovialedison');
Query OK, 1 row affected (0.01 sec)

mysql> admin check table t1;
ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x1, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xf8}, colEndOffsets:[]uint16{0xa}}, index:types.Datum{k:0x1, decimal:0x0, length:0x0, i:1, collation:"", b:[]uint8(nil), x:interface {}(nil)} != record:<nil>

mysql> select * from t1;
+-------+--------------+
| c_int | c_str        |
+-------+--------------+
|     1 | jovialedison |
+-------+--------------+
1 row in set (0.01 sec)

mysql> select * from t1 use index(c_int);
+-------+-------+
| c_int | c_str |
+-------+-------+
|     1 |       |
+-------+-------+
1 row in set (0.01 sec)

no prefix and still have problem

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions