--source include/have_innodb.inc

CREATE TABLE `test` (
  `date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ad_id` int(11) NOT NULL DEFAULT '0',
  `zone_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date_time`,`ad_id`,`zone_id`),
  KEY `index_ad_id` (`ad_id`),
  KEY `index_zone_id` (`zone_id`),
  KEY `index_date_time` (`date_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test partition by Range(year(date_time)*12+month(date_time)) (
partition p201401 values less than ((2014*12)+1),
partition p201402 values less than ((2014*12)+2),
partition p201403 values less than ((2014*12)+3),
partition p201404 values less than ((2014*12)+4),
partition p201405 values less than ((2014*12)+5),
partition p201406 values less than ((2014*12)+6),
partition p201407 values less than ((2014*12)+7),
partition p201408 values less than ((2014*12)+8),
partition p201409 values less than ((2014*12)+9),
partition p201410 values less than ((2014*12)+10),
partition p201411 values less than ((2014*12)+11),
partition p201412 values less than ((2014*12)+12),
partition pmax    values less than (MAXVALUE)
);

--disable_query_log
let $i=1000;
while ($i)
{
--eval insert into test(date_time, ad_id, zone_id) values(cast(adddate(curdate(), interval -$i%365 day) as datetime), $i, $i)
--dec $i
}
--enable_query_log

--vertical_results
explain partitions SELECT * FROM test WHERE date_time = '1982-06-23';
explain partitions SELECT * FROM test WHERE date_time BETWEEN '1991-02-15' AND '1991-02-25';
explain partitions SELECT * FROM test WHERE date_time >= '1984-06-21' AND date_time <= '1985-06-21';