Bug #72689 Partitioning with YEARWEEK

• Submitted: 20 May 2014 7:21
• Modified: 21 Jun 2014 13:17
• Reporter: James Leung
• Status: No Feedback
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.5.12
• OS: Linux (Linux 2.6.18-128.el5)
• CPU Architecture: Any

[20 May 2014 7:21] James Leung

Description:
A table is partitioning by range with yearweek function.

When I try to do 'EXPLAIN PARTITIONS SELECT ... WHERE ...', I notice all partitions are inspected instead of only those containing the requested data.

Other user experimented similar problem:

http://forums.mysql.com/read.php?106,241102,241102

How to repeat:
1. create table:

CREATE TABLE `tst_tab` (
  `TST_DATE` date NOT NULL DEFAULT '0000-00-00',
  `ID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`TST_DATE`,`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEARWEEK(TST_DATE))
(PARTITION p201301 VALUES LESS THAN (201301) ENGINE = InnoDB,
 PARTITION p201302 VALUES LESS THAN (201302) ENGINE = InnoDB,
 PARTITION p201303 VALUES LESS THAN (201303) ENGINE = InnoDB,
 PARTITION p201304 VALUES LESS THAN (201304) ENGINE = InnoDB,
 PARTITION p201305 VALUES LESS THAN (201305) ENGINE = InnoDB,
 PARTITION p201306 VALUES LESS THAN (201306) ENGINE = InnoDB,
 PARTITION p201307 VALUES LESS THAN (201307) ENGINE = InnoDB,
 PARTITION p201308 VALUES LESS THAN (201308) ENGINE = InnoDB,
 PARTITION p201309 VALUES LESS THAN (201309) ENGINE = InnoDB,
 PARTITION p201310 VALUES LESS THAN (201310) ENGINE = InnoDB,
 PARTITION p201311 VALUES LESS THAN (201311) ENGINE = InnoDB,
 PARTITION p201312 VALUES LESS THAN (201312) ENGINE = InnoDB,
 PARTITION p201313 VALUES LESS THAN (201313) ENGINE = InnoDB,
 PARTITION p201314 VALUES LESS THAN (201314) ENGINE = InnoDB,
 PARTITION p201315 VALUES LESS THAN (201315) ENGINE = InnoDB,
 PARTITION p201316 VALUES LESS THAN (201316) ENGINE = InnoDB,
 PARTITION p201317 VALUES LESS THAN (201317) ENGINE = InnoDB,
 PARTITION p201318 VALUES LESS THAN (201318) ENGINE = InnoDB,
 PARTITION p201319 VALUES LESS THAN (201319) ENGINE = InnoDB,
 PARTITION p201320 VALUES LESS THAN (201320) ENGINE = InnoDB,
 PARTITION p201321 VALUES LESS THAN (201321) ENGINE = InnoDB,
 PARTITION p201322 VALUES LESS THAN (201322) ENGINE = InnoDB,
 PARTITION p201323 VALUES LESS THAN (201323) ENGINE = InnoDB,
 PARTITION p201324 VALUES LESS THAN (201324) ENGINE = InnoDB,
 PARTITION p201325 VALUES LESS THAN (201325) ENGINE = InnoDB,
 PARTITION p201326 VALUES LESS THAN (201326) ENGINE = InnoDB,
 PARTITION p201327 VALUES LESS THAN (201327) ENGINE = InnoDB,
 PARTITION p201328 VALUES LESS THAN (201328) ENGINE = InnoDB,
 PARTITION p201329 VALUES LESS THAN (201329) ENGINE = InnoDB,
 PARTITION p201330 VALUES LESS THAN (201330) ENGINE = InnoDB,
 PARTITION p201331 VALUES LESS THAN (201331) ENGINE = InnoDB,
 PARTITION p201332 VALUES LESS THAN (201332) ENGINE = InnoDB,
 PARTITION p201333 VALUES LESS THAN (201333) ENGINE = InnoDB,
 PARTITION p201334 VALUES LESS THAN (201334) ENGINE = InnoDB,
 PARTITION p201335 VALUES LESS THAN (201335) ENGINE = InnoDB,
 PARTITION p201336 VALUES LESS THAN (201336) ENGINE = InnoDB,
 PARTITION p201337 VALUES LESS THAN (201337) ENGINE = InnoDB,
 PARTITION p201338 VALUES LESS THAN (201338) ENGINE = InnoDB,
 PARTITION p201339 VALUES LESS THAN (201339) ENGINE = InnoDB,
 PARTITION p201340 VALUES LESS THAN (201340) ENGINE = InnoDB,
 PARTITION p201341 VALUES LESS THAN (201341) ENGINE = InnoDB,
 PARTITION p201342 VALUES LESS THAN (201342) ENGINE = InnoDB,
 PARTITION p201343 VALUES LESS THAN (201343) ENGINE = InnoDB,
 PARTITION p201344 VALUES LESS THAN (201344) ENGINE = InnoDB,
 PARTITION p201345 VALUES LESS THAN (201345) ENGINE = InnoDB,
 PARTITION p201346 VALUES LESS THAN (201346) ENGINE = InnoDB,
 PARTITION p201347 VALUES LESS THAN (201347) ENGINE = InnoDB,
 PARTITION p201348 VALUES LESS THAN (201348) ENGINE = InnoDB,
 PARTITION p201349 VALUES LESS THAN (201349) ENGINE = InnoDB,
 PARTITION p201350 VALUES LESS THAN (201350) ENGINE = InnoDB,
 PARTITION p201351 VALUES LESS THAN (201351) ENGINE = InnoDB,
 PARTITION p201352 VALUES LESS THAN (201352) ENGINE = InnoDB,
 PARTITION p201401 VALUES LESS THAN (201401) ENGINE = InnoDB,
 PARTITION p201402 VALUES LESS THAN (201402) ENGINE = InnoDB,
 PARTITION p201403 VALUES LESS THAN (201403) ENGINE = InnoDB,
 PARTITION p201404 VALUES LESS THAN (201404) ENGINE = InnoDB,
 PARTITION p201405 VALUES LESS THAN (201405) ENGINE = InnoDB,
 PARTITION p201406 VALUES LESS THAN (201406) ENGINE = InnoDB,
 PARTITION p201407 VALUES LESS THAN (201407) ENGINE = InnoDB,
 PARTITION p201408 VALUES LESS THAN (201408) ENGINE = InnoDB,
 PARTITION p201409 VALUES LESS THAN (201409) ENGINE = InnoDB,
 PARTITION p201410 VALUES LESS THAN (201410) ENGINE = InnoDB,
 PARTITION p201411 VALUES LESS THAN (201411) ENGINE = InnoDB,
 PARTITION p201412 VALUES LESS THAN (201412) ENGINE = InnoDB,
 PARTITION p201413 VALUES LESS THAN (201413) ENGINE = InnoDB,
 PARTITION p201414 VALUES LESS THAN (201414) ENGINE = InnoDB,
 PARTITION p201415 VALUES LESS THAN (201415) ENGINE = InnoDB,
 PARTITION p201416 VALUES LESS THAN (201416) ENGINE = InnoDB,
 PARTITION p201417 VALUES LESS THAN (201417) ENGINE = InnoDB,
 PARTITION p201418 VALUES LESS THAN (201418) ENGINE = InnoDB,
 PARTITION p201419 VALUES LESS THAN (201419) ENGINE = InnoDB,
 PARTITION p201420 VALUES LESS THAN (201420) ENGINE = InnoDB,
 PARTITION p201421 VALUES LESS THAN (201421) ENGINE = InnoDB,
 PARTITION p201422 VALUES LESS THAN (201422) ENGINE = InnoDB,
 PARTITION p201423 VALUES LESS THAN (201423) ENGINE = InnoDB,
 PARTITION p201424 VALUES LESS THAN (201424) ENGINE = InnoDB,
 PARTITION p201425 VALUES LESS THAN (201425) ENGINE = InnoDB,
 PARTITION p201426 VALUES LESS THAN (201426) ENGINE = InnoDB,
 PARTITION p201427 VALUES LESS THAN (201427) ENGINE = InnoDB,
 PARTITION p201428 VALUES LESS THAN (201428) ENGINE = InnoDB,
 PARTITION p201429 VALUES LESS THAN (201429) ENGINE = InnoDB,
 PARTITION p201430 VALUES LESS THAN (201430) ENGINE = InnoDB,
 PARTITION p201431 VALUES LESS THAN (201431) ENGINE = InnoDB,
 PARTITION p201432 VALUES LESS THAN (201432) ENGINE = InnoDB,
 PARTITION p201433 VALUES LESS THAN (201433) ENGINE = InnoDB,
 PARTITION p201434 VALUES LESS THAN (201434) ENGINE = InnoDB,
 PARTITION p201435 VALUES LESS THAN (201435) ENGINE = InnoDB,
 PARTITION p201436 VALUES LESS THAN (201436) ENGINE = InnoDB,
 PARTITION p201437 VALUES LESS THAN (201437) ENGINE = InnoDB,
 PARTITION p201438 VALUES LESS THAN (201438) ENGINE = InnoDB,
 PARTITION p201439 VALUES LESS THAN (201439) ENGINE = InnoDB,
 PARTITION p201440 VALUES LESS THAN (201440) ENGINE = InnoDB,
 PARTITION p201441 VALUES LESS THAN (201441) ENGINE = InnoDB,
 PARTITION p201442 VALUES LESS THAN (201442) ENGINE = InnoDB,
 PARTITION p201443 VALUES LESS THAN (201443) ENGINE = InnoDB,
 PARTITION p201444 VALUES LESS THAN (201444) ENGINE = InnoDB,
 PARTITION p201445 VALUES LESS THAN (201445) ENGINE = InnoDB,
 PARTITION p201446 VALUES LESS THAN (201446) ENGINE = InnoDB,
 PARTITION p201447 VALUES LESS THAN (201447) ENGINE = InnoDB,
 PARTITION p201448 VALUES LESS THAN (201448) ENGINE = InnoDB,
 PARTITION p201449 VALUES LESS THAN (201449) ENGINE = InnoDB,
 PARTITION p201450 VALUES LESS THAN (201450) ENGINE = InnoDB,
 PARTITION p201451 VALUES LESS THAN (201451) ENGINE = InnoDB,
 PARTITION p201452 VALUES LESS THAN (201452) ENGINE = InnoDB,
 PARTITION p201501 VALUES LESS THAN (201501) ENGINE = InnoDB,
 PARTITION p201502 VALUES LESS THAN (201502) ENGINE = InnoDB,
 PARTITION p201503 VALUES LESS THAN (201503) ENGINE = InnoDB,
 PARTITION p201504 VALUES LESS THAN (201504) ENGINE = InnoDB,
 PARTITION p201505 VALUES LESS THAN (201505) ENGINE = InnoDB,
 PARTITION p201506 VALUES LESS THAN (201506) ENGINE = InnoDB,
 PARTITION p201507 VALUES LESS THAN (201507) ENGINE = InnoDB,
 PARTITION p201508 VALUES LESS THAN (201508) ENGINE = InnoDB,
 PARTITION p201509 VALUES LESS THAN (201509) ENGINE = InnoDB,
 PARTITION p201510 VALUES LESS THAN (201510) ENGINE = InnoDB,
 PARTITION p201511 VALUES LESS THAN (201511) ENGINE = InnoDB,
 PARTITION p201512 VALUES LESS THAN (201512) ENGINE = InnoDB,
 PARTITION p201513 VALUES LESS THAN (201513) ENGINE = InnoDB,
 PARTITION p201514 VALUES LESS THAN (201514) ENGINE = InnoDB,
 PARTITION p201515 VALUES LESS THAN (201515) ENGINE = InnoDB,
 PARTITION p201516 VALUES LESS THAN (201516) ENGINE = InnoDB,
 PARTITION p201517 VALUES LESS THAN (201517) ENGINE = InnoDB,
 PARTITION p201518 VALUES LESS THAN (201518) ENGINE = InnoDB,
 PARTITION p201519 VALUES LESS THAN (201519) ENGINE = InnoDB,
 PARTITION p201520 VALUES LESS THAN (201520) ENGINE = InnoDB,
 PARTITION p201521 VALUES LESS THAN (201521) ENGINE = InnoDB,
 PARTITION p201522 VALUES LESS THAN (201522) ENGINE = InnoDB,
 PARTITION p201523 VALUES LESS THAN (201523) ENGINE = InnoDB,
 PARTITION p201524 VALUES LESS THAN (201524) ENGINE = InnoDB,
 PARTITION p201525 VALUES LESS THAN (201525) ENGINE = InnoDB,
 PARTITION p201526 VALUES LESS THAN (201526) ENGINE = InnoDB,
 PARTITION p201527 VALUES LESS THAN (201527) ENGINE = InnoDB,
 PARTITION p201528 VALUES LESS THAN (201528) ENGINE = InnoDB,
 PARTITION p201529 VALUES LESS THAN (201529) ENGINE = InnoDB,
 PARTITION p201530 VALUES LESS THAN (201530) ENGINE = InnoDB,
 PARTITION p201531 VALUES LESS THAN (201531) ENGINE = InnoDB,
 PARTITION p201532 VALUES LESS THAN (201532) ENGINE = InnoDB,
 PARTITION p201533 VALUES LESS THAN (201533) ENGINE = InnoDB,
 PARTITION p201534 VALUES LESS THAN (201534) ENGINE = InnoDB,
 PARTITION p201535 VALUES LESS THAN (201535) ENGINE = InnoDB,
 PARTITION p201536 VALUES LESS THAN (201536) ENGINE = InnoDB,
 PARTITION p201537 VALUES LESS THAN (201537) ENGINE = InnoDB,
 PARTITION p201538 VALUES LESS THAN (201538) ENGINE = InnoDB,
 PARTITION p201539 VALUES LESS THAN (201539) ENGINE = InnoDB,
 PARTITION p201540 VALUES LESS THAN (201540) ENGINE = InnoDB,
 PARTITION p201541 VALUES LESS THAN (201541) ENGINE = InnoDB,
 PARTITION p201542 VALUES LESS THAN (201542) ENGINE = InnoDB,
 PARTITION p201543 VALUES LESS THAN (201543) ENGINE = InnoDB,
 PARTITION p201544 VALUES LESS THAN (201544) ENGINE = InnoDB,
 PARTITION p201545 VALUES LESS THAN (201545) ENGINE = InnoDB,
 PARTITION p201546 VALUES LESS THAN (201546) ENGINE = InnoDB,
 PARTITION p201547 VALUES LESS THAN (201547) ENGINE = InnoDB,
 PARTITION p201548 VALUES LESS THAN (201548) ENGINE = InnoDB,
 PARTITION p201549 VALUES LESS THAN (201549) ENGINE = InnoDB,
 PARTITION p201550 VALUES LESS THAN (201550) ENGINE = InnoDB,
 PARTITION p201551 VALUES LESS THAN (201551) ENGINE = InnoDB,
 PARTITION p201552 VALUES LESS THAN (201552) ENGINE = InnoDB) */ |

2. insert a single record into table:
insert into tst_tab values ('2014-04-30', 1);

3. explain partition select statement:
select * from tst_tab where TST_DATE>='2014-04-01' and TST_DATE<='2014-04-30';

Result of explain partition should only involve partitions: 201413, 201414, 201415, 201416, 201417. But all partitions are involved.

Suggested fix:
Allow YEARWEEK has partitions pruning

[21 May 2014 13:17] MySQL Verification Team

Thank you for the bug report. Looks like YEARWEEK isn't included among the functions in range optimization:

http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-functions.html

"In MySQL 5.5, range optimization can be used for the TO_DAYS(), TO_SECONDS(), and YEAR() functions. In addition, beginning with MySQL 5.5.15, UNIX_TIMESTAMP() is treated as monotonic in partitioning expressions. See Section 19.4, “Partition Pruning”, for more information. "

[22 Jun 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".