Partitions usability I - blues and a preview

Pop quiz. Look at the picture below. Quick: can you tell me to which year and month partition P001 refers?


I couldn't say it without asking the database itself to revert the output of to_days:

select from_days(723180);
+-------------------+
| from_days(723180) |
+-------------------+
| 1980-01-01        | 
+-------------------+

Just to make the pain more clear, you do something like this, clean and neat, and you think you have done a good job.

CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
  partition p001 VALUES LESS THAN (to_days('2001-01-01')) 
, partition p002 VALUES LESS THAN (to_days('2001-02-01')) 
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

And then, what the database server is keeping, is really hard to use.

show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */

If you made a mistake and you look at the code, you will need to convert all the values using FROM_DAYS to make sure that the dates were as intended.
An additional pain is caused by the unbroken long line of partition definition. (Bug#14326). A patch was created, and it should eventually be applied soon.

New features around the corner

Well, not really around the corner, but there is hope that this enhancement will be available soon.
The change is conceptually simple, but momentous for users. You will be allowed to use native data types, without need of converting them to integers.
Here's how the above DDL will be rewritten:

CREATE TABLE t1 ( d DATE ) 
PARTITION by range column_list(d)
(
  partition p001 VALUES LESS THAN (column_list('2001-01-01')) 
, partition p002 VALUES LESS THAN (column_list('2001-02-01')) 
, partition p003 VALUES LESS THAN (column_list('2001-03-01'))
);

SHOW CREATE Table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE  COLUMN_LIST(d) 
(PARTITION p001 VALUES LESS THAN ( COLUMN_LIST('2001-01-01')) ENGINE = MyISAM,
PARTITION p002 VALUES LESS THAN ( COLUMN_LIST('2001-02-01')) ENGINE = MyISAM,
PARTITION p003 VALUES LESS THAN ( COLUMN_LIST('2001-03-01')) ENGINE = MyISAM) */

Not only you will use native data types without conversion, but the partition definition will be human readable. If you made a mistake somewhere, you will find out without need for recalculations.
When is the above marvel going to be available? Now!, with a Launchpad tree. Although I can't say when it will be released. It won't be in 5.1, because it's almost GA. It could be in a quick point release (5.2) or a major release (6.0).
And what about the error prone procedure of creating partitions? If I need to make monthly partitions from 1985 to 2008, doing that manually is going to be a pain, and a source of errors.
Stay tuned. I have a solution handy.
Till next time.