EXCHANGE PARTITION issue: Non-partitioned tables that nonmatch the partition definition can be swapped successfully

[snowballbear]

Bug Report

https://docs.pingcap.com/tidb/v6.5/partitioned-table#partition-management

1. Minimal reproduce step (Required)

partition table:
mysql> create table test_p(id int,create_ts datetime,name varchar(10)) partition by list columns(create_ts)(partition p20230829 values in ('2023-08-29'),partition p20230830 values in ('2023-08-30'));
Query OK, 0 rows affected (0.13 sec)
mysql> insert into test_p values (1,'2023-08-29','a');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_p values (2,'2023-08-30','b');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_p values (3,'2023-08-31','c');
ERROR 1526 (HY000): Table has no partition for value from column_list

non-partitioned table:
mysql> create table test_p_swap(id int,create_ts datetime,name varchar(10));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into test_p_swap values (3,'2023-08-31','c');
Query OK, 1 row affected (0.01 sec)

exchange partition:
mysql> alter table test_p EXCHANGE PARTITION p20230829 WITH TABLE test_p_swap;
Query OK, 0 rows affected, 1 warning (0.25 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1105 | after the exchange, please analyze related table of the exchange to update statistics |
+---------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_p;
+------+---------------------+------+
| id | create_ts | name |
+------+---------------------+------+
| 2 | 2023-08-30 00:00:00 | b |
| 3 | 2023-08-31 00:00:00 | c |
+------+---------------------+------+
2 rows in set, 1 warning (0.00 sec)

mysql> show create table test_p;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_p | CREATE TABLE test_p (
id int(11) DEFAULT NULL,
create_ts datetime DEFAULT NULL,
name varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY LIST COLUMNS(create_ts)
(PARTITION p20230829 VALUES IN ('2023-08-29'),
PARTITION p20230830 VALUES IN ('2023-08-30')) |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

The exchange partition statement will fail.

3. What did you see instead (Required)

Non-partitioned tables that nonmatch the partition definition can be swapped successfully.

4. What is your TiDB version? (Required)

v6.5.3

[jiyfhust]

/assign

[jackysp]

master also seems to have this bug. PTAL @mjonss

[mjonss]

@jiyfhust I did not mean to take over this issue, but I found some more issues with null handling, and created a PR to check and fix them as well.

There are some more tests needed, if you like please propose some, otherwise I will complete them soon, to get the PR ready for review.

Most likely there are more issues with null handling and VALIDATE of EXCHANGE PARTITION.

[jiyfhust]

There are some more tests needed, if you like please propose some, otherwise I will complete them soon, to get the PR ready for review.

Great, I'm glad you can fix them soon.

[jackysp]

cc @ryangao0513

[jackysp]

/found gs