MySQL Forums
Forum List  »  Partitioning

Re: Partition pruning when partition expression contains a function
Posted by: Mikael Ronström
Date: March 21, 2006 04:19PM

Hi,
No, this is not a bug, currently only TO_DAYS and YEAR functions are
supported for range optimisations. The major problem one bumps into when
performing range analysis is how to handle overflow. CAST as you use can
overflow, so if we should support we need to use care when evaluating the
range such that we don't have a range that passes an overflow boundary.
This is the major reason why so few functions are supported for range
optimisations. Even a very simple function as A + B where A and B are integers
can overflow and thus cause this issue. I'll give CAST some consideration
to see what we can with this, if it can be used at all or if it can be used
with a minor adaption.

Rgrds Mikael

ttsalone wrote:
> I partition a table using a partition expression
> that converts double values to integers with the
> CAST function.
>
> CREATE TABLE test (a DOUBLE, b DOUBLE, c, DOUBLE,
> time DATETIME)
> PARTITION BY RANGE (CAST(a AS UNSIGNED))
> (PARTITION p0 VALUES LESS THAN (50), PARTITION p1
> VALUES LESS THAN (100),
> PARTITION p2 VALUES LESS THAN (150), PARTITION p3
> VALUES LESS THAN (200),
> partition p4 VALUES LESS THAN MAXVALUE);
>
> The problem here is that the partition pruning
> doesn't seem to work(only the partition p1 should
> be scanned):
>
> mysql> explain partitions select count(*) from
> test where a > 80 and a < 90 and b > 120
> and b < 130;
> +----+-------------+-------+----------------+-----
> -+---------------+------+---------+------+-------+
> -------------+
> | id | select_type | table | partitions | type
> | possible_keys | key | key_len | ref | rows |
> Extra |
> +----+-------------+-------+----------------+-----
> -+---------------+------+---------+------+-------+
> -------------+
> | 1 | SIMPLE | test | p0,p1,p2,p3,p4 | ALL
> | NULL | NULL | NULL | NULL | 10000 |
> Using where |
> +----+-------------+-------+----------------+-----
> -+---------------+------+---------+------+-------+
> -------------+
> 1 row in set (0.00 sec)
>
> Is this a bug? I think that the query optimizer
> should evaluate the partition expression for the
> relevant predicates in the query before deciding
> which partitions to scan.

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partition pruning when partition expression contains a function
2453
March 21, 2006 04:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.