MySQL Bugs: #36758: Partition table selection failing for some time functions under InnoDB

Bug #36758	Partition table selection failing for some time functions under InnoDB
Submitted:	16 May 2008 14:43	Modified:	21 Jul 2008 12:20
Reporter:	Ben Clewett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.24	OS:	Linux
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	innodb, partition

Description:
Where a partition is formed from a YEAR(t) where t is a DATE, DATETIME, TIMESTAMP, the manual suggests time functions such as:

SELECT WHERE YEAR(t) =

Should be able to select the correct partition table.  Such as the given example:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

However under InnoDB at least this is not the case.  All partition tables are used.

Therefore a table partitioned based on a function of time where time is also a key which is used extensively, cannot be accessed without hitting every table in the partition.  This seriously limits partitioning ability.

How to repeat:
Create the table from the URL with ENGINE=InnoDB.

The execute EXPLAIN PARTITIONS with the query:

SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;

The manual suggests only partition p2 should be hit.  Where as the explain shows:

d: 1
select_type: SIMPLE
table: employees
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where; Using temporary; Using filesort

I have further noted the these time comparisons seem to work:

WHERE t IN
WHERE t =

However these do not hit the right partition:

WHERE t >
WHERE t BETWEEN
WHERE YEAR(t) =

Suggested fix:
Ensure the example in the manual works and please allow the other examples to work as well.

Thank you for the report.

Verfieid as documentation bug. Please see more detailed information at http://s.petrunia.net/blog/?p=21 and bug #36604

This was documented according to instructions from the Partitioning developers, and the pruning examples in the Manual worked as shown at the time they were written. In addition, I don't recall being informed about any difference in behaviour due to a change in storage engine (other than NDB, where some partitioning types are unsupported).

If there have been changes in the server's behaviour, then this should be considered a server bug.

Jon,

thank you for the feedback. Moving category back to server.

IMO, this is a documentation bug.
Instead of 

SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;

the query should be:

SELECT COUNT(*) FROM employees WHERE separated between '2000-01-01' and '2000-12-31' GROUP BY store_id;

Stefan, No need to fight. I am sure Jon agrees with me ;)

#
# Here is the example from the documentation.
#
mysql> drop table if exists employees;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE employees (
    ->             id INT NOT NULL,
    ->             fname VARCHAR(30),
    ->             lname VARCHAR(30),
    ->             hired DATE NOT NULL DEFAULT '1970-01-01',
    ->             separated DATE NOT NULL DEFAULT '9999-12-31',
    ->             job_code INT NOT NULL,
    ->             store_id INT NOT NULL
    -> )engine=innodb
    -> PARTITION BY RANGE ( YEAR(separated) ) ( 
    ->             PARTITION p0 VALUES LESS THAN (1991),
    ->             PARTITION p1 VALUES LESS THAN (1996),
    ->             PARTITION p2 VALUES LESS THAN (2001),
    ->             PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.11 sec)

#
# This is the query from the manual. Partition pruning does not kick.
#
mysql> EXPLAIN PARTITIONS 
    -> SELECT COUNT(*) FROM employees 
    -> WHERE YEAR(separated) = 2000 
    -> GROUP BY store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

#
# This is the correct query: Partition pruning kicks in
#
mysql> EXPLAIN PARTITIONS 
    -> SELECT COUNT(*) FROM employees \
    -> WHERE separated between '2000-01-01' and '2000-12-31' 
    -> GROUP BY store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Used the query suggested by Giuseppe.