Bug #28928 UNIX_TIMESTAMP() should be considered unary monotonic by partition pruning
Submitted: 6 Jun 2007 14:03 Modified: 13 Jun 2011 13:21
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[6 Jun 2007 14:03] Sergey Petrunya
Description:
Partition pruning does not consider UNIX_TIMESTAMP() to be unary monotonic increasing function. 

How to repeat:
create table some_events(event_date datetime, other_info char(100)) 
partition by range(UNIX_TIMESTAMP(event_date))
( 
  partition p0 values less than (unix_timestamp('2007-01-01 00:00')),
  partition p1 values less than (unix_timestamp('2007-01-01 08:00')),
  partition p2 values less than (unix_timestamp('2007-01-01 16:00')),
  partition p3 values less than (unix_timestamp('2007-01-02 00:00')),
  partition p4 values less than (unix_timestamp('2007-01-02 08:00')),
  partition p5 values less than (unix_timestamp('2007-01-02 16:00'))
);

insert into some_events values 
 ('2007-01-01 01:00', 'event1'),
 ('2007-01-01 09:00', 'event2'),
 ('2007-01-01 17:00', 'event3');

insert into some_events values 
 ('2007-01-02 01:00', 'event1'),
 ('2007-01-02 09:00', 'event2'),

explain partitions  select * from some_events where event_date between '2007-01-01 19:00' and '2007-01-02 04:00'; 
+----+-------------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | partitions        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | some_events | p0,p1,p2,p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where | 
+----+-------------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

All partitions are used, while scanning partitions p3 and p4 would have been
enough.

Suggested fix:
Make UNIX_TIMESTAMP() to be considered unary monotonic function. This is around 5 lines of code.

This would be a very natural thing to do, because we have YEAR() and TO_DAYS() recognized as monotonic. UNIX_TIMESTAMP() is a natural conitinuation in this series, it will be used by those who want to have > 1 partition per day.
[22 Nov 2007 11:21] Mattias Jonsson
In a future release there will be a function to_sec() for this use.
UNIX_TIMESTAMP() is not unary monotonic, since it is only 32-bit and partitioning functions should be 64-bit. For large date it returns 0.
[4 Feb 2010 6:42] Sveta Smirnova
Bug #50899 was marked as duplicate of this one.
[3 Feb 2011 16:41] Laurent Bigonville
Hi,

Now that UNIX_TIMESTAMP() is needed to partition by TIMESTAMP column, I guess that this bug must be reopened

Cheers
[4 Feb 2011 13:07] Mattias Jonsson
I'm resetting this to verified. There should be possible to support range pruning for UNIX_TIMESTAMP().
[4 Feb 2011 14:09] Bugs System
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/130417

3576 Mattias Jonsson	2011-02-04
      Bug#28928: UNIX_TIMESTAMP() should be considered unary monotonic by partition pruning
      
      Preliminary patch to support range pruning for UNIX_TIMESTAMP.
      
      TODO: Fix the tests, add better tests.
[2 Mar 2011 4:56] Lei Zou
Does any MySQL release contain the fix for bug 28928? Please advice. We need this patch badly. Thanks.
[16 Mar 2011 11:10] Mattias Jonsson
The patch is not pushed to any production tree yet. I just recommitted the patch with more tests.
[29 Apr 2011 9:56] Mattias Jonsson
Closed bug#60440 as a duplicate of this.
[7 Jun 2011 16:30] Jean-Francois Dionne
Hi, any update on that? I'm pretty sure I'm not the only one waiting for the patch...
[13 Jun 2011 13:21] Jon Stephens
Documented as follows in the 5.5.15 and 5.6.13 changelogs.

      The UNIX_TIMESTAMP() function was not treated an a monotonic function for
      purposes of partition pruning.

Also updated info regarding functions in Partitioning Limitations.

Closed.