MySQL Bugs: #27927: Partition pruning not optimal with TO

Bug #27927	Partition pruning not optimal with TO_DAYS function
Submitted:	18 Apr 2007 12:53	Modified:	24 Sep 2007 12:42
Reporter:	David Shrewsbury	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.17	OS:	Linux
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
When a table is partitioned with range partitioning using the TO_DAYS() function, the optimizer does not properly prune the partitions in certain comparisons (less than, greater than). This does not happen when range partitioning is used without the TO_DAYS() function.

See below for examples.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
ENGINE=MyISAM
PARTITION BY RANGE( TO_DAYS(recdate) ) (
  PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
  PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
);

INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
INSERT INTO t1 VALUES ('2007-03-15 12:00:00');

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( id INT NOT NULL )
ENGINE=MyISAM
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10)
);

INSERT INTO t2 VALUES (1),(5),(7);

mysql> explain partitions select * from t1 where recdate < '2007-03-08 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

mysql> explain partitions select * from t2 where id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: p0
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:

Suggested fix:
The optimizer should recognize that the query on table t1 is a LESS THAN comparison and know that partition p1 does not need to be searched. I would expect results like the query on t2, which is correct.

The work around for the query on table t1 is to use a LESS THAN/EQUAL TO comparison with the maximum value from partition p0:

mysql> explain partitions select * from t1 where recdate <= '2007-03-07 23:59:59'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where

Thank you for the report.

Verified as described.

Analysis:

The predicate
  
  recdate < '2007-03-08 00:00:00'

is processed as follows: TO_DAYS(datetime) is known as MONOTONIC_INCREASING, i.e. the optimizer knows about TO_DAYS that it satisfies

  F() is unary and (x < y) ==> (F(x) <= F(y))  

so, 

  recdate < '2007-03-08 00:00:00'

gets converted to

  TO_DAYS(recdate) <= TO_DAYS('2007-03-08 00:00:00')

The "or-equal" is why partition p1 is used.

...So the observed effect is kind of expected.

A possible solution
-------------------

At the moment the only functions that are known to be MONOTONIC_INCREASING are:

  TO_DAYS(datetime_field), YEAR(date_column), YEAR(datetime_column)

For those functions, greater-than comparisons can only be converted to greater-or-equal:

  (field > const) -> (FUNC(field) >= FUNC(const))

But less-than comparisons can be converted to less-than (and not less-or-equal):

  (field < const) -> (FUNC(val) < FUNC(const))  (*)

This makes it possible to make the following fix: 

* Change the meaning of MONOTONIC_INCREASING so it allows conversions like (*);

* Replace declaration/use partition_info::range_analysis_include_bounds so
  that conversion (*) takes place.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34021

ChangeSet@1.2603, 2007-09-11 02:17:19+04:00, sergefp@mysql.com +7 -0
  BUG#27927 Partition pruning not optimal with TO_DAYS and YEAR functions
  - Introduced val_int_endpoint() function which converts between func 
    argument intervals and func value intervals for monotonic functions.
  - Made partition interval analyzer use part_expr->val_int_endpoint()
    to check if the edge values should be included.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34257

ChangeSet@1.2603, 2007-09-14 14:18:42+04:00, sergefp@mysql.com +8 -0
  BUG#27927:Partition pruning not optimal with TO_DAYS and YEAR functions
  - Introduced val_int_endpoint() function which converts between func 
    argument intervals and func value intervals for monotonic functions.
  - Made partition interval analyzer use part_expr->val_int_endpoint()
    to check if the edge values should be included.

Pushed into 5.1.23-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented as follows in 5.1.23 changelog:

          Partition pruning was not used for queries having
          <= or >= conditions in the WHERE clause on a table
          using TO_DAYS() in the partitioning expression.