Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12395

DROP PARTITION does not work as expected when table has DEFAULT LIST partition

    XMLWordPrintable

    Details

      Description

      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
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: