MySQL Bugs: #24245: Partitioning on TIMESTAMP column

Bug #24245	Partitioning on TIMESTAMP column
Submitted:	13 Nov 2006 9:59	Modified:	25 Nov 2006 1:24
Reporter:	Christian Starka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.14-BK, 5.1.12 beta	OS:	Linux (Linux, Win2003Server)
Assigned to:		CPU Architecture:	Any

Description:
> I want to use partitioning by range, based on a 
> TIMESTAMP(14) column, using the year-function. But 
> when explaining the query, i can see: 
> - always all partitions involved, independently 
> from the year-range that i'm querying 
> - some special characters in front of the first 
> created partitions. 

Answer from forum:
YEAR(DATETIME) works for partition pruning, YEAR(TIMESTAMP) 
should probably work as well but isn't activated in the 
code today.  

How to repeat:
> CREATE TABLE `T` ( 
> `rowid` BIGINT UNSIGNED NOT NULL, 
> `ref` BIGINT UNSIGNED NOT NULL, 
> `lastchange` TIMESTAMP(14) DEFAULT '0000-00-00 
> 00:00:00', 
> `msec` INTEGER(3) UNSIGNED, 
> `value` FLOAT, 
> `status` BIGINT UNSIGNED, 
> `lastchange_event` TIMESTAMP(14), 
> `msec_event` INTEGER(3) UNSIGNED, 
> `zaediff` FLOAT, 
> `target` VARCHAR(1) DEFAULT 'C', 
> `expire` TIMESTAMP(14), 
> `seq_num` BIGINT UNSIGNED, 
> UNIQUE `UK_REF_LASTCHANGE`(`ref`,`lastchange`) 
> ) ENGINE=MyISAM 
> PARTITION BY RANGE ( YEAR(lastchange)) ( 
> PARTITION p0 VALUES LESS THAN (2006), 
> PARTITION p1 VALUES LESS THAN (2007), 
> PARTITION p2 VALUES LESS THAN (2008), 
> PARTITION p3 VALUES LESS THAN (2009), 
> PARTITION p4 VALUES LESS THAN (2010) 
> );

insert data into different partitions ....

explain partitions select * from t where lastchange between <date1> and <date2>

Thank you for a problem report. Verified just as described, also - with 5.1.14-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.14-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `T` (
    ->  `rowid` BIGINT UNSIGNED NOT NULL,
    ->  `ref` BIGINT UNSIGNED NOT NULL,
    ->  `lastchange` TIMESTAMP(14) DEFAULT '0000-00-00
    '>  00:00:00',
    ->  `msec` INTEGER(3) UNSIGNED,
    ->  `value` FLOAT,
    ->  `status` BIGINT UNSIGNED,
    ->  `lastchange_event` TIMESTAMP(14),
    ->  `msec_event` INTEGER(3) UNSIGNED,
    ->  `zaediff` FLOAT,
    ->  `target` VARCHAR(1) DEFAULT 'C',
    ->  `expire` TIMESTAMP(14),
    ->  `seq_num` BIGINT UNSIGNED,
    ->  UNIQUE `UK_REF_LASTCHANGE`(`ref`,`lastchange`)
    ->  ) ENGINE=MyISAM
    ->  PARTITION BY RANGE ( YEAR(lastchange)) (
    ->  PARTITION p0 VALUES LESS THAN (2006),
    ->  PARTITION p1 VALUES LESS THAN (2007),
    ->  PARTITION p2 VALUES LESS THAN (2008),
    ->  PARTITION p3 VALUES LESS THAN (2009),
    ->  PARTITION p4 VALUES LESS THAN (2010)
    ->  );
Query OK, 0 rows affected, 3 warnings (0.04 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1541
Message: The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5
.2. Please use 'TIMESTAMP' instead
*************************** 2. row ***************************
  Level: Warning
   Code: 1541
Message: The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5
.2. Please use 'TIMESTAMP' instead
*************************** 3. row ***************************
  Level: Warning
   Code: 1541
Message: The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 5
.2. Please use 'TIMESTAMP' instead
3 rows in set (0.00 sec)

mysql> insert into `T` (rowid, ref, lastchange) values(1, 2, now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into `T` (rowid, ref, lastchange) values(2, 3, '2005-01-01');
Query OK, 1 row affected (0.02 sec)

mysql> insert into `T` (rowid, ref, lastchange) values(3, 4, '2008-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `T` (rowid, ref, lastchange) values(7, 5, '2009-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> explain partitions select * from `T` where lastchange between now() and
'2007-01-01';
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
| id | select_type | table | partitions     | type | possible_keys | key  | key_
len | ref  | rows | Extra       |
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
|  1 | SIMPLE      | T     | p0,p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL
    | NULL |    5 | Using where |
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from `T` where lastchange between '2007-01-0
1' and '2008-01-01';
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
| id | select_type | table | partitions     | type | possible_keys | key  | key_
len | ref  | rows | Extra       |
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
|  1 | SIMPLE      | T     | p0,p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL
    | NULL |    5 | Using where |
+----+-------------+-------+----------------+------+---------------+------+-----
----+------+------+-------------+
1 row in set (0.01 sec)

Test case:

CREATE TABLE `T` (
`rowid` BIGINT UNSIGNED NOT NULL,
`ref` BIGINT UNSIGNED NOT NULL,
`lastchange` TIMESTAMP(14) DEFAULT '0000-00-00 00:00:00',
UNIQUE `UK_REF_LASTCHANGE`(`ref`,`lastchange`)
) ENGINE=MyISAM
PARTITION BY RANGE ( YEAR(lastchange)) (
PARTITION p0 VALUES LESS THAN (2006),
PARTITION p1 VALUES LESS THAN (2007),
PARTITION p2 VALUES LESS THAN (2008),
PARTITION p3 VALUES LESS THAN (2009),
PARTITION p4 VALUES LESS THAN (2010));

insert into `T` (rowid, ref, lastchange) values(1, 2, now());
insert into `T` (rowid, ref, lastchange) values(2, 3, '2005-01-01');
insert into `T` (rowid, ref, lastchange) values(3, 4, '2008-01-01');
insert into `T` (rowid, ref, lastchange) values(7, 5, '2009-01-01');
explain partitions select * from `T` where lastchange between '2007-01-01' and '2008-01-01';

Please note that, technically, this report is "not a bug".
The MySQL Reference Manual states that partition pruning 
for temporal values happens only for DATE and DATETIME 
columns; see:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
which says:
"[Partition pruning] can also be employed when the partitioning expression represents an increasing or decreasing relationship or uses a function such as YEAR() or TO_DAYS() that produces an integer value when applied to a DATE or DATETIME column value."

The workaround for this problem is to use the DATETIME data 
type rather than TIMESTAMP for columns in tables that will 
be partitioned on a temporal value.

As previously indicated, pruning is not expected to work for tables partitioned on a TIMESTAMP column, and you should use a DATETIME or DATE column for this instead.

If you need to preserve the date/time when a row was inserted or updated, you can do one of the following:

1. Supply the datetime as part of an INSERT or UPDATE statement

2. Set an appropriate INSERT or UPDATE trigger (or both) on the table. 

If you're not familiar with MySQL triggers, see http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.