MySQL Forums
Forum List  »  Partitioning

Bug in LESS THAN combined with TO_DAYS?
Posted by: Guillaume Métayer
Date: September 14, 2009 03:27PM

I created a partitioned table, both with MyISAM and InnoDB, and have the same problem.

My first partition should take records where the date is LESS THAN TO_DAYS('2009-01-01'), my second where the date is LESS THAN TO_DAYS('2009-02-01'), and so on.

For some reason, when I query on any *first* of the month + a few more days, two partitions are being used, because it seems the first of the month is part of the previous partition... But LESS THAN is supposed to be smaller than and does *not* include the value you pass the expression, right? Here's the SHOW CREATE table of my test table:

CREATE TABLE `y` (
`stat_date` date NOT NULL,
`site_id` int(11) NOT NULL,
`name` varchar(30) NOT NULL,
`visits` int(11) DEFAULT '0',
KEY `stat_date` (`stat_date`,`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(stat_date)) (PARTITION p0 VALUES LESS THAN (733773) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (733804) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (733832) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (733863) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (733893) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (733924) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (733954) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (733985) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (734016) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (734046) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN (734077) ENGINE = InnoDB, PARTITION p11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

I checked and TO_DAYS('2009-01-01') is 733773, so it ain't a partition creation error.

Any idea? Is that a bug with LESS THAN or the combination of LESS THAN and PARTITION BY RANGE (to_days(field)) in MySQL 5.1?

Options: ReplyQuote


Subject
Views
Written By
Posted
Bug in LESS THAN combined with TO_DAYS?
3389
September 14, 2009 03:27PM


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.