MySQL Forums
Forum List  »  Partitioning

Partition pruning when partition expression contains a function
Posted by: ttsalone
Date: March 20, 2006 01:36AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition pruning when partition expression contains a function
3570
March 20, 2006 01:36AM


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.