Description:
Tables using LIST COLUMN_LIST partitioning are only having the last defined partition established:
For the following table definition (from the attached test case):
CREATE TABLE `D` (
`int_signed` int(11) DEFAULT NULL,
`date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST COLUMN_LIST(int_signed)
(PARTITION p0 VALUES IN ( COLUMN_LIST(1), COLUMN_LIST(3), COLUMN_LIST(5), COLUMN_LIST(7), COLUMN_LIST(9), COLUMN_LIST(NULL)) ,
PARTITION p1 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(8), COLUMN_LIST(0)),
PARTITION p2 VALUES IN ( COLUMN_LIST(4), COLUMN_LIST(6))) */;
INSERT INTO `D` VALUES (NULL,NULL);
INSERT INTO `D` VALUES (2,'2005-09-11');
The INSERT (2,...) record produces the following error:
mysqltest: At line 17: query 'INSERT INTO `D` VALUES (2,'2005-09-11')' failed: 1526: Table has no partition for value from column list
For some reason the (NULL,NULL) INSERT *is* processed, but trying to insert any other values defined for partitions p0 or p1 will fail in a similar fashion
How to repeat:
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings
CREATE TABLE `D` (
`int_signed` int(11) DEFAULT NULL,
`date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST COLUMN_LIST(int_signed)
(PARTITION p0 VALUES IN ( COLUMN_LIST(1), COLUMN_LIST(3), COLUMN_LIST(5), COLUMN_LIST(7), COLUMN_LIST(9), COLUMN_LIST(NULL)) ,
PARTITION p1 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(8), COLUMN_LIST(0)),
PARTITION p2 VALUES IN ( COLUMN_LIST(4), COLUMN_LIST(6))) */;
INSERT INTO `D` VALUES (NULL,NULL);
#INSERT INTO `D` VALUES (2,'2005-09-11');
#INSERT INTO `D` VALUES (8,'2001-03-25');
INSERT INTO `D` VALUES (6,'2000-02-10');
INSERT INTO `D` VALUES (1,'2003-09-20');
INSERT INTO `D` VALUES (NULL,'2008-06-18');
INSERT INTO `D` VALUES (3,'2004-10-15');
INSERT INTO `D` VALUES (7,'1999-12-31');
INSERT INTO `D` VALUES (7,'2001-01-01');
INSERT INTO `D` VALUES (3,'2004-07-04');
INSERT INTO `D` VALUES (3,'1995-06-17');
SELECT `int_signed`
FROM `D` ;
#/* Diff: */
#/* --- /tmp//randgen10143-1254325455-server0.dump 2009-09-30 11:44:15.000000000 -0400
# +++ /tmp//randgen10143-1254325455-server1.dump 2009-09-30 11:44:15.000000000 -0400
# @@ -1,4 +1,10 @@
# +1
# 2
# +3
# +3
# +3
# 6
# +7
# +7
# 8
# 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` (
`varchar_10` varchar(10) DEFAULT NULL,
`varchar_5` varchar(5) DEFAULT NULL,
`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 LINEAR HASH ( to_seconds(`date`))
SUBPARTITIONS 4
(PARTITION p0 VALUES IN ( COLUMN_LIST(1), COLUMN_LIST(3), COLUMN_LIST(5), COLUMN_LIST(7), COLUMN_LIST(9), COLUMN_LIST(NULL)) ENGINE = MyISAM,
PARTITION p1 VALUES IN ( COLUMN_LIST(2), COLUMN_LIST(4), COLUMN_LIST(6), COLUMN_LIST(8), COLUMN_LIST(0)) ENGINE = MyISAM) */;
INSERT INTO `D` VALUES ('k','s','2001-06-23 00:00:00',NULL,NULL);
INSERT INTO `D` VALUES (NULL,'u',NULL,2,'2005-09-11');
INSERT INTO `D` VALUES ('l','x',NULL,8,'2001-03-25');
INSERT INTO `D` VALUES (NULL,'q','2008-06-18 00:00:00',6,'2000-02-10');
SELECT table1 . `date` AS field1, table1 . `datetime_key` AS field2, table1 . `int_signed` AS field3
FROM `D` AS table1
WHERE ( ( ( ( ( table1 . `int_signed` < table1 . `int_signed` ) AND table1 . `int_signed` IN ( 9, 7, 2 ) ) OR table1 . `int_signed` NOT BETWEEN 4 AND ( 4 + 3 ) ) OR table1 . `varchar_5` IN ('c', 'h' ) ) AND table1 . `varchar_5` >= table1 . `varchar_10` );
#/* Diff: */
#/* --- /tmp//randgen10143-1254325456-server0.dump 2009-09-30 11:44:16.000000000 -0400
# +++ /tmp//randgen10143-1254325456-server1.dump 2009-09-30 11:44:16.000000000 -0400
# @@ -1 +1,4 @@
# +0000-00-00 0000-00-00 00:00:00 3
# +0000-00-00 2000-11-03 11:44:44 3
# 2001-03-25 NULL 8
# +2008-05-24 0000-00-00 00:00:00 3 */
DROP TABLE D;
#/* End of test case for query 1 */
Suggested fix:
Ensure partition definitions that are accepted by the server are working or throw an error if my definitions are somehow incorrect.