| 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: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[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.
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.