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.
Subject
Views
Written By
Posted
Partition pruning when partition expression contains a function
3570
March 20, 2006 01:36AM
2453
March 21, 2006 04:19PM
2144
April 11, 2006 11:59AM
2564
April 12, 2006 12:02PM
2166
June 05, 2006 02:51PM
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.