Partitioning Limitations Relating to Functions


This section discusses limitations in MariaDB Partitioning relating specifically to functions used in partitioning expressions.

Only the MariaDB functions shown in the following table are supported in partitioning expressions.

ABS() CEILING() (see CEILING() and FLOOR()) DAY()
DAYOFMONTH() DAYOFWEEK() DAYOFYEAR()
DATEDIFF() EXTRACT() (see EXTRACT() function with WEEK specifier) FLOOR() (see CEILING() and FLOOR())
HOUR() MICROSECOND() MINUTE()
MOD() MONTH() QUARTER()
SECOND() TIME_TO_SEC() TO_DAYS()
TO_SECONDS() UNIX-TIMESTAMP() (permitted beginning with MariaDB 5.6.1 and fully supported beginning with MariaDB 5.6.3, with TIMESTAMP columns) WEEKDAY()
YEAR() YEARWEEK()

In MariaDB 5.6, partition pruning is supported for the TO-DAYS(), TO_SECONDS(), and YEAR() functions. In addition, beginning with MariaDB 5.6.3, UNIX_TIMESTAMP() is treated as monotonic in partitioning expressions. See , "Partition Pruning", for more information.

CEILING() and FLOOR(). Each of these functions returns an integer only if it is passed an argument of an exact numeric type, such as one of the INT types or DECIMAL. This means, for example, that the following CREATE TABLE statement fails with an error, as shown here:

mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
 -> PARTITION p0 VALUES IN (1,3,5),
 -> PARTITION p1 VALUES IN (2,4,6)
 -> );
ERROR 1490 (HY000): The PARTITION function returns the wrong type

EXTRACT() function with WEEK specifier. The value returned by the EXTRACT() function, when used as EXTRACT(WEEK FROM col), depends on the value of the default-week-format system variable. For this reason, beginning with MariaDB 5.6.2, EXTRACT() is longer permitted as a partitioning function when it specifies the unit as WEEK. (Bug #54483)

See , "Mathematical Functions", for more information about the return types of these functions, as well as , "Numeric Types".

Retornar