Skip to content
Home » MySQL » How to Resolve ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

How to Resolve ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

ERROR 1486 (HY000)

When we partitioned a table by a timestamp-typed column ORDER_TIME in table ORDERS, it generated an error 1486 like this:

mysql> ALTER TABLE orders PARTITION BY LIST(year(order_time)) (PARTITION p2013 VALUES IN (2013) ) ;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

Since the function YEAR() could return different results by different time zones, especially when the timestamp are around the conjunctions of adjacent years. In other words, YEAR() is indeterministic for timestamp. Therefore, YEAR() can not be used as the boundary of timestamp-based partitions.

A solution to this problem is to use UNIX_TIMESTAMP instead of YEAR, but first, you should know the boundary of UNIX_TIMESTAMP between year 2013 and 2014.

mysql> select unix_timestamp('2014-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('2014-01-01 00:00:00') |
+---------------------------------------+
|                            1388505600 |
+---------------------------------------+
1 row in set (0.00 sec)

Now, let's try to create partitions again.

mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

This time, we create the partition successfully.

Let's see the query route.

mysql> explain partitions select * from orders;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | orders | p2013      | ALL  | NULL          | NULL | NULL    | NULL |   12 |       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Yes, the partition is working now.

An alternative is to give up using the timestamp-typed column as the partition key and use a date-typed column instead, in this case, we call it "order_date" as the partition key:

mysql> ALTER TABLE orders PARTITION BY RANGE(year(order_date)) SUBPARTITION BY HASH(month(order_date)) SUBPARTITIONS 12 (PARTITION p2013 VALUES LESS THAN (2014) , PARTITION p2014 VALUES LESS THAN (2015) ) ;
Query OK, 10 rows affected (0.25 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE orders DROP PARTITION p2014 ;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

Leave a Reply

Your email address will not be published. Required fields are marked *