[#MDEV-12395] DROP PARTITION does not work as expected when table has DEFAULT LIST partition

DROP PARTITION x is supposed to delete all data stored in the partition x. It is documented, for example, in MySQL manual, and probably hidden or implied here in the KB (for a note, there could be a better documentation for partition maintenance).

Normally, it works this way – with RANGE partitioning, whether or not the MAXVALUE partition is involved.

MariaDB [test]> CREATE TABLE trange (i INT) PARTITION BY RANGE (i) (PARTITION p VALUES LESS THAN (5), PARTITION pmax VALUES LESS THAN (MAXVALUE));

Query OK, 0 rows affected (0.45 sec)

MariaDB [test]> INSERT INTO trange VALUES (1),(10);

Query OK, 2 rows affected (0.04 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE trange DROP PARTITION p;

Query OK, 0 rows affected (0.31 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM trange;

+------+

| i    |

+------+

|   10 |

+------+

1 row in set (0.00 sec)

It also works with LIST partitioning when the table only has "normal" list partitions:

MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION p2 VALUES IN (10));

Query OK, 0 rows affected (0.48 sec)

MariaDB [test]> INSERT INTO tlist VALUES (1),(10);

Query OK, 2 rows affected (0.06 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE tlist DROP PARTITION p;

Query OK, 0 rows affected (0.35 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM tlist;

+------+

| i    |

+------+

|   10 |

+------+

1 row in set (0.00 sec)

But it does not work when a LIST-partitioned table has the DEFAULT-partition.

In this case, when a "normal" partition is dropped, values are transferred to the default partition:

MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION pdef DEFAULT);

Query OK, 0 rows affected (0.42 sec)

MariaDB [test]> INSERT INTO tlist VALUES (1),(10);

Query OK, 2 rows affected (0.05 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE tlist DROP PARTITION p;

Query OK, 2 rows affected (1.24 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM tlist;

+------+

| i    |

+------+

|    1 |

|   10 |

+------+

2 rows in set (0.00 sec)

And if the default partition is being dropped, it causes an error:

MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION pdef DEFAULT);

Query OK, 0 rows affected (0.40 sec)

MariaDB [test]> INSERT INTO tlist VALUES (1),(10);

Query OK, 2 rows affected (0.05 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE tlist DROP PARTITION pdef;

ERROR 1526 (HY000): Table has no partition for value 10

[MDEV-12395] DROP PARTITION does not work as expected when table has DEFAULT LIST partition Created: 2017-03-29 Updated: 2017-04-07 Resolved: 2017-04-07
Status:	Closed
Project:	MariaDB Server
Component/s:	Partitioning
Affects Version/s:	10.2
Fix Version/s:	10.2.6

[MDEV-12395] DROP PARTITION does not work as expected when table has DEFAULT LIST partition Created: 2017-03-29 Updated: 2017-04-07 Resolved: 2017-04-07