Skip navigation links
MySQL 5.1 Reference Manual :: 17 Partitioning :: 17.5 Restrictions and Limitations on Partitioning :: 17.5.3 Partitioning Limitations Relating to Functions
18 Stored Programs and Views »
Section Navigation      [Toggle]

17.5.3. Partitioning Limitations Relating to Functions

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

Beginning with MySQL 5.1.12, only the MySQL 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()
UNIX_TIMESTAMP() (permitted in MySQL 5.1.43 and later, with TIMESTAMP columns) WEEKDAY() YEAR()
  YEARWEEK()  

In MySQL 5.1, partition pruning is supported only for the TO_DAYS() and YEAR() functions. See Section 17.4, “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 MySQL 5.1.55, EXTRACT() is longer permitted as a partitioning function when it specifies the unit as WEEK. (Bug #54483)

See Section 11.6.2, “Mathematical Functions”, for more information about the return types of these functions, as well as Section 10.2, “Numeric Types”.

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Chris Wagner on August 19 2011 10:11pm[Delete] [Edit]

The bit shift operators >> and << can be emulated in the partitioning function by DIV and multiplication. This is because shift is identical to multiplying or dividing by 2 on an integer. I'm using POW here just to illustrate the relationship between the functions. U can't use POW so write the actual product in the function as in my e.g.

`int` >> num --> `int` DIV POW(2, num)
`int` << num --> `int` * POW(2, num)

e.g.
`int` >> 8 is `int` DIV 256

Add your own comment.