Bug #47753 Partition by LIST COLUMN_LIST appears to require an order for value list items
Submitted: 30 Sep 2009 20:24 Modified: 21 Oct 2009 13:18
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5 WL#3352 OS:Any
Assigned to: Mikael Ronström CPU Architecture:Any
Tags: LIST COLUMN_LIST, ordered entry, partitioning
Triage: Triaged: D2 (Serious)

[30 Sep 2009 20:24] Patrick Crews 
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)
[21 Oct 2009 13:18] Mikael Ronström 
Bug fixed by patch in BUG#47752