Table Partitioning Limitations with MariaDB Xpand

Unsupported in Partitioned Tables

This is the list of specific MySQL Partitioned Table features that are unsupported in Xpand.

• LIST, HASH, KEY, LINEAR KEY partitions

• SUBPARTITIONS

• Partitioning across functions such as: ABS(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), DATEDIFF(), EXTRACT(), MICROSECOND(), MINUTE(), MOD(), MONTH(), HOUR(), SECOND(), QUARTER(), TIME_TO_SEC(), WEEKDAY()

• Temporary tables may not be partitioned

• COALESCE PARTITION number

• ANALYZE PARTITION {partition_names | ALL }

• CHECK PARTITION {partition_names | ALL }

• OPTIMIZE PARTITION {partition_names | ALL }

• REBUILD PARTITION {partition_names | ALL }

• REPAIR PARTITION {partition_names | ALL }

• EXPLAIN PARTITION

• SELECT FROM table_name PARTITION {partition_name | ALL}

Caveats of Using Partitioned Tables

• Xpand recommends no more than 64 partitions per table. The maximum number permitted is configurable via the global variable max_partitions.

• To determine the number of slices utilized by a partition, SHOW FULL CREATE TABLE and count the number of HASHES.

• A partition's ordinal position (the consecutive sequence in which a partition was added to the database) is unavailable in Xpand.

• Adding data to a table that does not fit within any partition will cause this error

  ERROR 1526 (HY000): [6147] Table has no partition for value: nnn

• Altering a partitioned table and modifying characteristics of a column used for partitioning requires that you respecify partitioning ranges for the table. For example:

  sql> /* Given this table definition: */
  sql> CREATE TABLE test (id INT KEY)
       PARTITION BY RANGE (id)
       (PARTITION p0 VALUES LESS THAN (1000),
       PARTITION p1 VALUES LESS THAN (2000));
       Query OK, 0 rows affected (0.19 sec)
  
  sql> /* Changing characteristics of the partitioned column causes this ALTER to fail: */
  sql> ALTER TABLE test
       MODIFY COLUMN id INT AUTO_INCREMENT;
       ERROR 1 (HY000): [12298] Invalid constraint specification: Index
       test.__idx_test__PRIMARY on a partitioned table must contain the partition column
  
  sql> /* But respecifying the partitioning details allows this ALTER to succeed: */
  sql> ALTER TABLE test
       MODIFY COLUMN id INT AUTO_INCREMENT
       PARTITION BY RANGE (id)
       (PARTITION p0 VALUES LESS THAN (1000),
       PARTITION p1 VALUES LESS THAN (2000));
       Query OK, 0 rows affected (3.98 sec)
  

• If partitioning using a partition key that includes the YEAR datatype, you must also use the YEAR function as shown in this example:

  sql> CREATE TABLE sample_year_partitioning (year_col YEAR PRIMARY KEY)
       PARTITION BY RANGE (YEAR(year_col))
       (PARTITION p0 VALUES LESS THAN (5),
       PARTITION p1 VALUES LESS THAN (10),
       PARTITION p2 VALUES LESS THAN MAXVALUE);