| Bug #45531 | don't correct optimizer partition's data query | ||
|---|---|---|---|
| Submitted: | 16 Jun 2009 13:59 | Modified: | 20 Jun 2009 14:22 |
| Reporter: | guanding jin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.1.32,5.1.34 | OS: | Linux (Linux/Solaris) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Partition pruning | ||
[16 Jun 2009 16:25]
Valeriy Kravchuk
Thank you for the problem report. Dump of performance_data table's data is missing for a complete test case. Can you, please, upload it?
[16 Jun 2009 16:37]
Sveta Smirnova
Thank you for the report. But according to http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html this is not a bug: ----<q>----- Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. Note We plan to add pruning support in a future MySQL release for additional functions that act on a DATE or DATETIME value, return an integer, and are increasing or decreasing. ----</q>---- As you partition by WEEKDAY.
[20 Jun 2009 14:22]
guanding jin
DELIMITER $$
DROP PROCEDURE IF EXISTS usp_Performance_data$$
CREATE PROCEDURE usp_Performance_data(IN dCreateDate DATETIME)
BEGIN
DECLARE iService_id,iNum,iDateNum SMALLINT UNSIGNED DEFAULT 1;
DECLARE iserviceNum,ids_name SMALLINT DEFAULT 0;
DECLARE dStartDate DATETIME;
SET dStartDate=DATE_FORMAT(dCreateDate,'%Y-%m-%d');
SET iserviceNum=65;
WHILE iDateNum<=2016 DO
WHILE iNum<=50 DO
SET iserviceNum=iserviceNum + 1;
START TRANSACTION;
WHILE iService_id<=10 DO
SET ids_name=ids_name+ iService_id;
INSERT INTO performance_data(service_id,service_name,gmt_create,ds_name,value)
VALUES(iService_id,CONCAT(CHAR(iserviceNum),CHAR(iserviceNum+10),CHAR(iserviceNum+20)),dStartDate,CONCAT(CHAR(ids_name),CHAR(ids_name+10),CHAR(ids_name+20)),RAND());
SET iService_id=iService_id+1;
END WHILE;
COMMIT;
SET ids_name=66;
SET iService_id=1;
SET iNum=iNum+1;
END WHILE;
SET iserviceNum=65;
SET iNum=1;
SET dStartDate=DATE_ADD(dStartDate,INTERVAL 5 MINUTE);
SET iDateNum=iDateNum+1;
END WHILE;
END $$
DELIMITER ;
CALL usp_Performance_data('2009-06-10');
.....use weekday to partition.....my english is no good
Description: table definition's SQL： CREATE TABLE `performance_data_list` ( `service_id` int(11) DEFAULT NULL, `service_name` varchar(32) COLLATE utf8_bin DEFAULT NULL, `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ds_name` varchar(32) COLLATE utf8_bin DEFAULT NULL, `value` double DEFAULT NULL, `weeknum` smallint(6) NOT NULL DEFAULT '0', KEY `weeknum` (`gmt_create`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin PARTITION BY LIST (WEEKDAY(gmt_create)) (PARTITION pmonday VALUES IN (0) ENGINE = MyISAM, PARTITION ptuesday VALUES IN (1) ENGINE = MyISAM, PARTITION pwednesday VALUES IN (2) ENGINE = MyISAM, PARTITION pthursday VALUES IN (3) ENGINE = MyISAM, PARTITION pfriday VALUES IN (4) ENGINE = MyISAM, PARTITION psaturday VALUES IN (5) ENGINE = MyISAM, PARTITION psunday VALUES IN (6) ENGINE = MyISAM); INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=0 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=1 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=2 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=3 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=4 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=5 limit 1000; INSERT INTO performance_data_list SELECT * FROM performance_data where weeknum=6 limit 1000; version:mysql-5.1.32 explain's information: root@localhost : dragoon 09:39:35> EXPLAIN PARTITIONS SELECT * FROM performance_data_list WHERE gmt_create >='2009-06-08' AND gmt_create <'2009-06-08 02:00:00'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: performance_data_list partitions: pmonday,ptuesday,pwednesday,pthursday,pfriday,psaturday,psunday type: range possible_keys: weeknum key: weeknum key_len: 4 ref: NULL rows: 1004 Extra: Using where 1 row in set (0.00 sec) ####################this is incorrect########################## records should be in partition's pmonday ####################*****************########################## root@localhost : dragoon 09:43:56> EXPLAIN PARTITIONS SELECT * FROM performance_data_list WHERE gmt_create ='2009-06-08 00:10:00'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: performance_data_list partitions: pmonday type: ref possible_keys: weeknum key: weeknum key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec) ####################*****************########################## this is correct ####################*****************########################## How to repeat: version:mysql-5.1.34 explain's information: root@localhost : dragoon 09:39:35> EXPLAIN PARTITIONS SELECT * FROM performance_data_list WHERE gmt_create >='2009-06-08' AND gmt_create <'2009-06-08 02:00:00'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: performance_data_list partitions: pmonday type: range possible_keys: weeknum key: weeknum key_len: 4 ref: NULL rows: 1004 Extra: Using where 1 row in set (0.00 sec) ####################*****************########################## this is correct ####################*****************########################## root@localhost : dragoon 09:43:56> EXPLAIN PARTITIONS SELECT * FROM performance_data_list WHERE gmt_create ='2009-06-08 00:10:00'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: performance_data_list partitions: pmonday type: ref possible_keys: weeknum key: weeknum key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec) ####################*****************########################## this is correct ####################*****************########################## root@localhost : dragoon 09:39:35> EXPLAIN PARTITIONS SELECT * FROM performance_data_list WHERE gmt_create >='2009-06-08 00:10:00' AND gmt_create <'2009-06-09 00:10:00'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: performance_data_list partitions: pmonday,ptuesday,pwednesday,pthursday,pfriday,psaturday,psunday type: range possible_keys: weeknum key: weeknum key_len: 4 ref: NULL rows: 2000 Extra: Using where 1 row in set (0.00 sec) ####################this is incorrect########################## records should be in partition's pmonday and ptuesday ####################*****************##########################