Support default partition for list (columns) partition table

[crazycs520]

Feature Request

Is your feature request related to a problem? Please describe:

N/A

Describe the feature you'd like:

The default partition of list partition table is from ORACLE, MYSQL doesn't support this, but it is a very useful feature.

Here is an example, I want to create a list partition table, the list partition depends on the year.

create table t (a date) partition by list (year(a)) (
    partition p0 values in (2020));

But I execute the following SQL will fail:

> insert into t values ('2019-10-27 17:36:10');
(1526, 'Table has no partition for value 2019')

I know the upper behavior is expected, So I want to add a default partition to store the data that doesn't match the other partitions. Such as:

create table t (a date) partition by list (year(a)) (
    partition p0 values in (2020), 
    partition p1 values in (default));

Then the following SQL will be successfully inserted into the default partition.

insert into t values ('2019-10-27 17:36:10'),('2021-10-27 17:36:10'),('2000-10-27 17:36:10');

------------------------------------------------------------------------------------------------

Another feature-request is, should we support the ORACLE list partition syntax? Some customers are using ORACLE syntax to test the list partition.

Describe alternatives you've considered:

The upper features aren't hard to implement, I already implemented this here: bb7133#33

Teachability, Documentation, Adoption, Migration Strategy:

N/A

[crazycs520]

@zz-jason PTAL

[zz-jason]

I noticed that there is a worklog in MySQL which discusses to support the default list partition: WL#5781: DEFAULT/catch-all partition for LIST partitioning, the feature request in MySQL is Bug #50566.

The Syntax is like Oracle:

CREATE TABLE t (a INT, b TEXT)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,5,6,7),
 PARTITION p1 VALUES IN (3,4,9),
 PARTITION pDefault VALUES IN (DEFAULT));

Please note:

When using a DEFAULT partition, it will not longer be possible to ADD a partition, but one must instead REORGANIZE the DEFAULT partition, just like MAXVALUE partition in RANGE partitioning.

[zz-jason]

Another feature-request is, should we support the ORACLE list partition syntax? Some customers are using ORACLE syntax to test the list partition.

@crazycs520 I think it's better to keep the MySQL syntax since we are claimed to be MySQL compatible. BTW, it's better to discuss only one feature request at one issue.

[zz-jason]

This feature request LGTM

[mjonss]

/component tablepartition