MySQL Bugs: #47752: Partition by LIST COLUMN_LIST only recognizing last defined partition

Bug #47752	Partition by LIST COLUMN_LIST only recognizing last defined partition
Submitted:	30 Sep 2009 17:56	Modified:	12 Nov 2009 12:03
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	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:	DDL, LIST COLUMN_LIST, partitioning

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.

I had used a fixed variable which wasn't set, this led
to that list values weren't in sorted order, which meant
that the search failed miserably when defining the entries
in non-sorted order. Also had missed a few variables in the
qsort call.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85372

2880 Mikael Ronstrom	2009-10-01
      BUG#47752, missed to sort values in list partitioning
      modified:
        sql/partition_info.cc
        sql/sql_partition.cc

Pushed into 6.0.14-alpha (revid:mikael@mysql.com-20091104090210-om5lq1v39ppduu0e) (version source revid:mikael@mysql.com-20091030163450-387z4yevx0lrj3fb) (merge vers: 6.0.14-alpha) (pib:13)

Discussed with Mikael; he confirmed that this fix was pushed together with main push for WL#3352; closed without further action.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091028172236-0v5j962mh2opxpkj) (merge vers: 5.5.0-beta) (pib:13)

Re-closed; see previous comments.