Converting a partitioned table from a normal table results in duplicate data for the primary key

[harry1129]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(id bigint,id_1 bigint,name varchar(10),primary key (id) NONCLUSTERED);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values(1,1,'AA'),(2,2,'BB'),(3,3,'CC'),(4,4,'DD');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t1 partition by hash(id_1) partitions 4;
Query OK, 0 rows affected, 1 warning (1.58 sec)

mysql> insert into t1 values(2,1,'AA');
Query OK, 1 row affected (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.3
Edition: Community
Git Commit Hash: 70bfd90035cc81e80d78b4f1f76ca4baef8a5756
Git Branch: HEAD
UTC Build Time: 2024-07-31 13:17:12
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

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

mysql> alter table t1 partition by hash(id_1) partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

3. What did you see instead (Required)

mysql> alter table t1 partition by hash(id_1) partitions 4;
Query OK, 0 rows affected, 1 warning (1.58 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.3
Edition: Community
Git Commit Hash: 70bfd90035cc81e80d78b4f1f76ca4baef8a5756
Git Branch: HEAD
UTC Build Time: 2024-07-31 13:17:12
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

PS: v8.3.0 does not reproduce this issue

[harry1129]

This may result in incorrect data for the query

mysql> create table t1(id bigint,id_1 bigint,name varchar(10),primary key (id) CLUSTERED);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values(1,1,'AA'),(2,2,'BB'),(3,3,'CC'),(4,4,'DD');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t1 partition by hash(id_1) partitions 4;
Query OK, 0 rows affected, 1 warning (0.78 sec)

mysql> insert into t1 values(2,1,'AA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  3 |    3 | CC   |
|  1 |    1 | AA   |
|  2 |    1 | AA   |
|  4 |    4 | DD   |
|  2 |    2 | BB   |
+----+------+------+
5 rows in set, 1 warning (0.01 sec)

mysql> select * from t1 partition(p0);
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  4 |    4 | DD   |
+----+------+------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from t1 partition(p1);
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  1 |    1 | AA   |
|  2 |    1 | AA   |
+----+------+------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from t1 partition(p2);
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  2 |    2 | BB   |
+----+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1 partition(p3);
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  3 |    3 | CC   |
+----+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> analyze table t1;
Query OK, 0 rows affected, 8 warnings (0.09 sec)

mysql> select * from t1 where id in (1,2,3);
+----+------+------+
| id | id_1 | name |
+----+------+------+
|  1 |    1 | AA   |
|  2 |    2 | BB   |
|  3 |    3 | CC   |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.5.3
Edition: Community
Git Commit Hash: 70bfd90035cc81e80d78b4f1f76ca4baef8a5756
Git Branch: HEAD
UTC Build Time: 2024-07-31 13:17:12
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[mjonss]

Seems to affect 7.5 series, not 7.1 or 8.0/8.1.