Bug #41739 Partition Pruning doesn't work if partitioning function has some calculation.
Submitted: 25 Dec 2008 7:54 Modified: 14 Jan 2009 10:43
Reporter: Sadao Hiratsuka (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.30, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Optimizer, partitioning, qc
Triage: Triaged: D5 (Feature request)

[25 Dec 2008 7:54] Sadao Hiratsuka
Description:
Partition Pruning doesn't work if partitioning function has some calculation.

How to repeat:
***** good case

mysql> create table ptest_ok (
    ->  id int auto_increment,
    ->  created_at datetime,
    ->  data varchar(100),
    ->  primary key (id, created_at)
    -> )
    -> partition by range (to_days(created_at)) (
    ->  partition p20090101 values less than (to_days('2009-01-01 00:00:00')),
    ->  partition p20090201 values less than (to_days('2009-02-01 00:00:00')),
    ->  partition p20090301 values less than (to_days('2009-03-01 00:00:00')),
    ->  partition p_last values less than maxvalue
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> explain partitions select * from ptest_ok where created_at < '2009-01-15 12:34:56'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ptest_ok
   partitions: p20090101,p20090201
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

***** bad case

mysql> create table ptest_ng_simple (
    ->  id int auto_increment,
    ->  created_at datetime,
    ->  data varchar(100),
    ->  primary key (id, created_at)
    -> )

    -> partition by range (to_days(created_at) + 1) (     <===== *here*

    ->  partition p20090101 values less than (to_days('2009-01-01 00:00:00')),
    ->  partition p20090201 values less than (to_days('2009-02-01 00:00:00')),
    ->  partition p20090301 values less than (to_days('2009-03-01 00:00:00')),
    ->  partition p_last values less than maxvalue
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> explain partitions select * from ptest_ng_simple where created_at < '2009-01-15 12:34:56'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ptest_ng_simple
   partitions: p20090101,p20090201,p20090301,p_last     <====== *here*
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

Suggested fix:
Actually, I want such a following partitioned table.
(and mysqld can't optimize it.)

create table ptest_ng (
 id int auto_increment,
 created_at datetime,
 data varchar(100),
 primary key (id, created_at)
)
partition by range ((to_days(created_at) - 730000) * 86400 + time_to_sec(created_at)) (
 partition p200901010600 values less than
  ((to_days('2009-01-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-01-01 06:00:00')),
 partition p200902010600 values less than
  ((to_days('2009-02-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-02-01 06:00:00')),
 partition p200903010600 values less than
  ((to_days('2009-03-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-03-01 06:00:00')),
 partition p_last values less than maxvalue
);

So suggested fix is one of:
- support unix_timestamp() for partitioning function.
- support pruning partition even if partitioning function has some calcuration.
[25 Dec 2008 12:18] Sveta Smirnova
Thank you for the report.

Verified as described, although it is more likely feature request.
[14 Jan 2009 10:43] Mikael Ronström
In WL#3352 there is a new function to_seconds which will
make it possible to partition on a second level which
removes the need here to do calculations. Calculations
and partition pruning is a won't fix.

It is currently not decided which version WL#3352 will
be integrated into, there is a public tree available
with it.