Exchange List Partition add validition check tests for null value handling and default partition

[jiyfhust]

          Please create a followup issue, to test null handling in validation, since I assume there may be hidded issues in non-list cases?

Originally posted by @mjonss in #46021 (review)

[jiyfhust]

Related #46492

[jiyfhust]

Fault case 1:

First create  table t1 and t2:

CREATE TABLE t1 (id int default null, c varchar(128)) PARTITION BY HASH (id) PARTITIONS 3;
CREATE TABLE t2 (id int default null, c varchar(128));

Test:

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

mysql> select * from t1 partition(p0);
+------+-------+
| id   | c     |
+------+-------+
| NULL | aaaaa |
+------+-------+
1 row in set, 1 warning (0.00 sec)

That is row(null, 'aaaaaa') should in partition p0.

mysql> insert into t2 values(null, 'bbbbb');
Query OK, 1 row affected (0.01 sec)

Table t2 has a row(null, 'bbbbb'),  like row(null, 'aaaaa'), it can only exists in p0 if insert into t1.
So we expect the exchange fails, but it sucesss.

mysql> alter table t1 EXCHANGE PARTITION p2 WITH TABLE t2; 
Query OK, 0 rows affected, 1 warning (0.21 sec)

we check data in t1;

mysql> select *from t1 partition(p0);
+------+-------+
| id   | c     |
+------+-------+
| NULL | aaaaa |
+------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select *from t1 partition(p2);
+------+-------+
| id   | c     |
+------+-------+
| NULL | bbbbb |
+------+-------+
1 row in set, 1 warning (0.00 sec)

Not expected.

[jiyfhust]

Fault case 2:

First create  table t1 and t2:

CREATE TABLE t1 (id int default null, c varchar(128)) partition by range (id)(
		partition p0 values less than (10), 
		partition p1 values less than (20));
CREATE TABLE t2 (id int default null, c varchar(128));

Test:

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

mysql> select * from t1 partition(p0);
+------+-------+
| id   | c     |
+------+-------+
| NULL | aaaaa |
+------+-------+
1 row in set, 1 warning (0.00 sec)

That is row(null, 'aaaaaa') should in partition p0.

mysql> insert into t2 values(null, 'bbbbb');
Query OK, 1 row affected (0.01 sec)

Table t2 has a row(null, 'bbbbb'),  like row(null, 'aaaaa'), it can only exists in p0 if insert into t1.
So we expect the exchange fails, but it sucesss.

mysql> alter table t1 EXCHANGE PARTITION p1 WITH TABLE t2; 
Query OK, 0 rows affected, 1 warning (0.21 sec)

we check data in t1;

mysql> select *from t1 partition(p0);
+------+-------+
| id   | c     |
+------+-------+
| NULL | aaaaa |
+------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select *from t1 partition(p1);
+------+-------+
| id   | c     |
+------+-------+
| NULL | bbbbb |
+------+-------+
1 row in set, 1 warning (0.00 sec)

Not expected.

[jiyfhust]

case 3:

TiDB support defalut list partition:

mysql> select version();
+----------------------------------------+
| version()                              |
+----------------------------------------+
| 8.0.11-TiDB-v5.5.0-alpha-5320-ge8247b5 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (id int, c varchar(128)) partition by list columns(c)(partition p0 values in ('a'), partition p1 values in (default));
Query OK, 0 rows affected (0.13 sec)

mysql do not support list defalut partition:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (id int, c varchar(128)) partition by list columns(c)(partition p0 values in ('a'), partition p1 values in (default));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1

TiDB Exchange list default partition:

mysql> CREATE TABLE t2 (id int, c varchar(128));
Query OK, 0 rows affected (0.12 sec)

mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t2           |
+--------------+
2 rows in set (0.00 sec)

mysql> alter table t1 EXCHANGE PARTITION p1 WITH TABLE t2; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 51 near ") limit 1"

It reported syntax error.

[jiyfhust]

/type bug

[jiyfhust]

/label affects-7.5

[jiyfhust]

/severity moderate