Prunning partitions with range by date
Hi all,
I created the following test table:
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ins_date` datetime NOT NULL,
PRIMARY KEY (`id`, `ins_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( TO_DAYS(`ins_date`) ) (
PARTITION pre_year VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION cur_year_jan VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION cur_year_feb VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION cur_year_mar VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION cur_year_may VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION cur_year_apr VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION cur_year_jun VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION cur_year_jul VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION cur_year_aug VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION cur_year_sep VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION cur_year_oct VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION cur_year_nov VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION cur_year_dec VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION nex_year VALUES LESS THAN (MAXVALUE)
);
The table has these partitions:
- one partition for all records prior to 2012-01-01
- 12 partions for year 2012 (one for each month)
- one partition for all records after 2102-12-31
The objective was to test prunning behaivor. To achieve this, I inserted some records into this table:
-- Records for 2011
insert into test set ins_date = '2011-01-01';
insert into test set ins_date = '2011-02-02';
insert into test set ins_date = '2011-03-03';
insert into test set ins_date = '2011-04-04';
insert into test set ins_date = '2011-05-05';
insert into test set ins_date = '2011-06-06';
insert into test set ins_date = '2011-07-07';
insert into test set ins_date = '2011-08-08';
insert into test set ins_date = '2011-09-09';
insert into test set ins_date = '2011-10-10';
insert into test set ins_date = '2011-11-11';
insert into test set ins_date = '2011-12-12';
-- Records for 2012
insert into test set ins_date = '2012-01-01';
insert into test set ins_date = '2012-02-02';
insert into test set ins_date = '2012-03-03';
insert into test set ins_date = '2012-04-04';
insert into test set ins_date = '2012-05-05';
insert into test set ins_date = '2012-06-06';
insert into test set ins_date = '2012-07-07';
insert into test set ins_date = '2012-08-08';
insert into test set ins_date = '2012-09-09';
insert into test set ins_date = '2012-10-10';
insert into test set ins_date = '2012-11-11';
insert into test set ins_date = '2012-12-12';
-- Records for 2013
insert into test set ins_date = '2013-01-01';
insert into test set ins_date = '2013-02-02';
insert into test set ins_date = '2013-03-03';
insert into test set ins_date = '2013-04-04';
insert into test set ins_date = '2013-05-05';
insert into test set ins_date = '2013-06-06';
insert into test set ins_date = '2013-07-07';
insert into test set ins_date = '2013-08-08';
insert into test set ins_date = '2013-09-09';
insert into test set ins_date = '2013-10-10';
insert into test set ins_date = '2013-11-11';
insert into test set ins_date = '2013-12-12';
So I executed this select statement
EXPLAIN PARTITIONS SELECT * FROM test WHERE ins_date >= '2012-09-01' AND ins_date <= '2012-09-30';
expecting that the list of prunned partitions would contain only "cur_year_sep", but the result was "pre_year,cur_year_sep".
I would like to know if is this correct or I might consider it a bug?
If I change the WHERE clause to "WHERE ins_date = '2012-09-09'" (using the "equals" operator) the explain statement shows me only "cur_year_sep". However, I need to make use of "greater than" and "lower than" operators.
Thanks in advance
Diogo