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