From 4aaabb06c011e20054613bff225823b3d66a135e Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Sep 2007 14:18:42 +0400 Subject: BUG#27927:Partition pruning not optimal with TO_DAYS and YEAR functions - Introduced val_int_endpoint() function which converts between func argument intervals and func value intervals for monotonic functions. - Made partition interval analyzer use part_expr->val_int_endpoint() to check if the edge values should be included. mysql-test/r/partition_pruning.result: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Testcase mysql-test/t/partition_pruning.test: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Testcase sql/item.cc: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Added Item_field::val_int_endpoint() implementation sql/item.h: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Added Item::val_int_endpoint() which converts intervals from argument space to function value space for unary monotonic functions. sql/item_timefunc.cc: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Added val_int_endpoint() for TO_DAYS and YEAR functions. sql/item_timefunc.h: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Added val_int_endpoint() for TO_DAYS and YEAR functions. sql/partition_info.h: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Removed partition_info::range_analysis_include_bounds as it is no longer needed. sql/sql_partition.cc: BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions - Make partition interval analyzer use part_expr->val_int_endpoint() to check if the edge values should be included. --- mysql-test/r/partition_pruning.result | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'mysql-test/r/partition_pruning.result') diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 776e6f3a15a..2dc37f7ed43 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -911,3 +911,31 @@ explain partitions select * from t1 where a>-2 and a <=0; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where drop table t1; +CREATE TABLE t1 ( recdate DATETIME NOT NULL ) +PARTITION BY RANGE( TO_DAYS(recdate) ) ( +PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ), +PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') ) +); +INSERT INTO t1 VALUES ('2007-03-01 12:00:00'); +INSERT INTO t1 VALUES ('2007-03-07 12:00:00'); +INSERT INTO t1 VALUES ('2007-03-08 12:00:00'); +INSERT INTO t1 VALUES ('2007-03-15 12:00:00'); +must use p0 only: +explain partitions select * from t1 where recdate < '2007-03-08 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where +drop table t1; +CREATE TABLE t1 ( recdate DATETIME NOT NULL ) +PARTITION BY RANGE( YEAR(recdate) ) ( +PARTITION p0 VALUES LESS THAN (2006), +PARTITION p1 VALUES LESS THAN (2007) +); +INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); +INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); +INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); +INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); +must use p0 only: +explain partitions select * from t1 where recdate < '2006-01-01 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where +drop table t1; -- cgit v1.2.1