MySQL Bugs: #14352: Partitions: range partition list can be out of order

Bug #14352	Partitions: range partition list can be out of order
Submitted:	26 Oct 2005 20:11	Modified:	29 Nov 2005 21:47
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Eric Herman	CPU Architecture:	Any

Description:
A list of range partitions should be in ascending order
by value. But I can construct expressions which evade
that rule.

How to repeat:
mysql> create table t3 (s1 int, primary key (s1)) engine=innodb partition by range (s1) (partition p1 values less than (case 1 when 1 then 2 else 99 end), partition p2 values less than (5));
Query OK, 0 rows affected (0.08 sec)

I was able to reproduce this bug using a recent 5.1 clone under FC4

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

When we do a "show create table t3;" we get the following:

CREATE TABLE `t3` (
   `s1` int(11) NOT NULL default '0',
   PRIMARY KEY  (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (s1)  
(PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION p2  
VALUES LESS THAN (5) ENGINE = InnoDB) |

looking at the show create line, it seems that it has been interpreted correctly.

When we change the case statement to read "(case 0 when 1 then 2 else  99 end)" then we get:

ERROR 1470 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

This error message could be a lot better, but this is "Not a Bug".