MySQL Bugs: #72238: Range Partition using Date functions not pruning for range query

Bug #72238	Range Partition using Date functions not pruning for range query
Submitted:	4 Apr 2014 15:20	Modified:	14 May 2014 14:13
Reporter:	Christopher Noyes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.36	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Server version: 5.5.36 MySQL Community Server (GPL) by Remi running on centos 5.7

documented in https://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html

states that these cases should work with a range partition

SELECT * FROM t2 WHERE dob = '1982-06-23';

UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

result from testing using explain partitions shows only in the first case are partitions pruned

How to repeat:

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)

Fill data for range of values

use explain partitions to verify partitions being selected

server variables

Attachment: variables.txt (text/plain), 8.22 KiB.

Thank you for the report.

Strictly speaking manual page you provided says: "Pruning can also be applied to short ranges, which the optimizer can convert into equivalent lists of values.", then "Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function." You use more complicated expression than simply YEAR() function and range of date larger than "short ranges, which the optimizer can convert into equivalent lists of values.". But I agree user manual is not clear what in this case partition pruning cannot happen. Particularly it does not say any word about how big should be range of values and which expressions can be used. So setting this report status to "Verified".

Experiments showed that for query SELECT * FROM test WHERE date_time >= '1984-06-21' AND date_time <= '1984-06-21'; partition pruning happens while for query SELECT * FROM test WHERE date_time >= '1984-06-21' AND date_time <= '1985-06-21'; does not.

test case for MTR

Attachment: bug72238.test (application/octet-stream, text), 1.58 KiB.

The documentation uses a partitioning expression of 'year(date)' which the pruning optimizer can use, but the table in this bug uses 'year(date_time)*12+month(date_time)' of which month() function is cyclic and cannot be used for pruning ranges, see bug#45350.

But in 5.5 you can use PARTITION BY RANGE COLUMNS (date) instead which works with native date columns, see:
https://dev.mysql.com/doc/refman/5.5/en/partitioning-columns-range.html

The problem with using ranges is maintaining partitions forward, adding new ranges and partitions automatically where adding ranges, requires a periodic DDL change on a Large table, that typically takes 1/2 hour to apply an alter table to.

If
ALTER TABLE t ADD PARTITION (pX VALUES LESS THAN (<curr_max> + N);
or
ALTER TABLE t REORGANIZE PARTITION p_last_empty INTO (PARTITION pX VALUES LESS THAN (<curr_max> + N), PARTITION p_last_empty VALUES LESS THAN MAXVALUE)
takes 30 min the I think it is a bug. Notice that in the REORGANIZE case you should keep the last partition empty, so no scanning/copying is needed when splitting it. The ALTER operation needs exclusive access to the table for a very short period for creating the new partition (or to split the empty last partition into two).

I do not see the connection between the bug and the last comment (since they are different things). Are you looking for something like bug#30346?
The main reason for not implementing this would be that a DML (INSERT/UPDATE) also could generate a DDL (ALTER TABLE ADD PARTITION) or an scheduled EVENT (which is possible since 5.1).