Description:
When using LIST COLUMN_LIST, it appears that the list entries that define a partition must be entered in a certain order:
This DDL works:
CREATE TABLE `D` (
`datetime_key` datetime DEFAULT NULL,
`int_signed` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST COLUMN_LIST(int_signed)
SUBPARTITION BY HASH ( to_seconds(`date`))
SUBPARTITIONS 2
(PARTITION p0 VALUES IN ( COLUMN_LIST(NULL), COLUMN_LIST(1), COLUMN_LIST(2), COLUMN_LIST(3), COLUMN_LIST(4), COLUMN_LIST(5), COLUMN_LIST(6), COLUMN_LIST(7), COLUMN_LIST(8), COLUMN_LIST(9), COLUMN_LIST(0)) ENGINE = MyISAM) */;
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',1,'2003-09-20');
INSERT INTO `D` VALUES (NULL,2,'2005-09-11');
INSERT INTO `D` VALUES (NULL,8,'2001-03-25');
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',3,'2008-05-24');
INSERT INTO `D` VALUES ('2004-07-07 16:41:37',7,NULL);
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',7,'2008-10-22');
However, if we move the 'COLUMN_LIST(NULL)' clause to the end of the list, we get failure:
mysqltest: At line 26: query 'INSERT INTO `D` VALUES ('2001-06-23 00:00:00',NULL,NULL)' failed: 1526: Table has no partition for value from column list
This is not limited to the NULL declaration. If we move the 'COLUMN_LIST(1)' behind 'COLUMN_LIST(2)', we get a failure on the first INSERT statement (trying to use 1 for `int_signed`)
This *may* be a documentation issue as this feature isn't fully documented, but it appears to be a departure from the behavior of regular LIST partitioning:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html
I also tested this against regular LIST partitioning (same as above, but sans the COLUMN_LIST usage) and I am allowed to enter values in any order without rejection - VALUES IN (NULL , 9, 2 , 3...)
How to repeat:
MTR test case:
Please note that 2,1,and NULL are not in the order they need to be for the test to work
Change the order to NULL, 1, 2 and the test will pass without incident.
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings
CREATE TABLE `D` (
`datetime_key` datetime DEFAULT NULL,
`int_signed` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST COLUMN_LIST(int_signed)
SUBPARTITION BY HASH ( to_seconds(`date`))
SUBPARTITIONS 2
(PARTITION p0 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(1), COLUMN_LIST(3), COLUMN_LIST(4), COLUMN_LIST(5), COLUMN_LIST(6), COLUMN_LIST(7), COLUMN_LIST(8), COLUMN_LIST(9), COLUMN_LIST(0), COLUMN_LIST(NULL)) ENGINE = MyISAM) */;
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',1,'2003-09-20');
INSERT INTO `D` VALUES (NULL,2,'2005-09-11');
INSERT INTO `D` VALUES (NULL,8,'2001-03-25');
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',3,'2008-05-24');
INSERT INTO `D` VALUES ('2004-07-07 16:41:37',7,NULL);
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',7,'2008-10-22');
INSERT INTO `D` VALUES ('2000-11-03 11:44:44',3,'0000-00-00');
INSERT INTO `D` VALUES ('0000-00-00 00:00:00',3,'0000-00-00');
INSERT INTO `D` VALUES ('2008-06-18 00:00:00',6,'2000-02-10');
INSERT INTO `D` VALUES ('2001-06-23 00:00:00',NULL,NULL);
SELECT `datetime_key`
FROM `D` ;
#/* Diff: */
#/* --- /tmp//randgen7101-1254340585-server0.dump 2009-09-30 15:56:25.000000000 -0400
# +++ /tmp//randgen7101-1254340585-server1.dump 2009-09-30 15:56:25.000000000 -0400
# @@ -3,6 +3,7 @@
# 0000-00-00 00:00:00
# 0000-00-00 00:00:00
# 2000-11-03 11:44:44
# +2001-06-23 00:00:00
# 2004-07-07 16:41:37
# 2008-06-18 00:00:00
# NULL */
DROP TABLE D;
#/* End of test case for query 0 */
#/* Begin test case for query 1 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings
CREATE TABLE `D` (
`int_signed_key` int(11) DEFAULT NULL,
`datetime_key` datetime DEFAULT NULL,
`int_signed` int(11) DEFAULT NULL,
`varchar_10_key` varchar(10) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`date` date DEFAULT NULL,
KEY `int_signed_key` (`int_signed_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_10_key` (`varchar_10_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST COLUMN_LIST(int_signed)
SUBPARTITION BY HASH ( to_seconds(`date`))
SUBPARTITIONS 2
(PARTITION p0 VALUES IN ( COLUMN_LIST(1), COLUMN_LIST(2), COLUMN_LIST(3), COLUMN_LIST(4), COLUMN_LIST(5), COLUMN_LIST(6), COLUMN_LIST(7), COLUMN_LIST(8), COLUMN_LIST(9), COLUMN_LIST(0), COLUMN_LIST(NULL)) ENGINE = MyISAM) */;
INSERT INTO `D` VALUES (1,'0000-00-00 00:00:00',1,'n','2000-12-26 00:00:00','2003-09-20');
INSERT INTO `D` VALUES (0,NULL,2,NULL,'0000-00-00 00:00:00','2005-09-11');
INSERT INTO `D` VALUES (6,NULL,8,'h','0000-00-00 00:00:00','2001-03-25');
INSERT INTO `D` VALUES (4,'0000-00-00 00:00:00',3,'k','2005-02-28 00:00:00','2008-05-24');
INSERT INTO `D` VALUES (9,'2004-07-07 16:41:37',7,'f','0000-00-00 00:00:00',NULL);
INSERT INTO `D` VALUES (1,'0000-00-00 00:00:00',7,'v','0000-00-00 00:00:00','2008-10-22');
INSERT INTO `D` VALUES (9,'2000-11-03 11:44:44',3,'s','2006-11-10 03:55:20','0000-00-00');
INSERT INTO `D` VALUES (NULL,'0000-00-00 00:00:00',3,'g','0000-00-00 00:00:00','0000-00-00');
INSERT INTO `D` VALUES (1,'2008-06-18 00:00:00',6,'h','2001-11-10 00:00:00','2000-02-10');
SELECT table1 . `date` AS field1, table2 . `datetime` AS field2, table1 . `date` AS field3, table2 . `datetime_key` AS field4
FROM `D` AS table1 INNER JOIN `D` AS table2 ON ( table2 . `varchar_10_key` = table1 . `varchar_10_key` )
WHERE ( ( ( table1 . `int_signed` BETWEEN 5 AND ( 5 + 9 ) ) AND table1 . `int_signed` = table1 . `int_signed_key` ) OR table1 . `int_signed` >= table1 . `int_signed_key` );
#/* Diff: */
#/* --- /tmp//randgen7101-1254340586-server0.dump 2009-09-30 15:56:26.000000000 -0400
# +++ /tmp//randgen7101-1254340586-server1.dump 2009-09-30 15:56:26.000000000 -0400
# @@ -3,4 +3,5 @@
# 2001-03-25 0000-00-00 00:00:00 2001-03-25 NULL
# 2001-03-25 2001-11-10 00:00:00 2001-03-25 2008-06-18 00:00:00
# 2003-09-20 2000-12-26 00:00:00 2003-09-20 0000-00-00 00:00:00
# +2003-09-20 2004-10-26 00:00:00 2003-09-20 2001-06-23 00:00:00
# 2008-10-22 0000-00-00 00:00:00 2008-10-22 0000-00-00 00:00:00 */
DROP TABLE D;
#/* End of test case for query 1 */
Suggested fix:
Either document this behavior or correct it to be more consistent with regular PARTITION BY LIST behavior (ie unordered lists)