MySQL Bugs: #18198: Expressions are allowed as partition functions

Bug #18198	Expressions are allowed as partition functions
Submitted:	13 Mar 2006 18:17	Modified:	12 Jul 2007 12:18
Reporter:	Reggie Burnett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1	OS:	Any (all)
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
We have several bugs open related to allowing expressions as the partition function.  This "feature" does not always work, therefore it should be disallowed.  All bugs related to this will be marked as "won't fix"

How to repeat:
Create a partitioned table something like this:

create table t1 (id int, name varchar(20)) partition by range (select * from t1)....

Suggested fix:
Don't allow

Thank you for the bug report.

Fairly complex bug, expected ETA 2 weeks

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/4487

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/4488

This will appear in 5.1.12

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html says:

"A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL."

Is this any different from that long-documented limitation?

Thanks!

I opened again because the original comment for this bug mentioned using (SELECT ...), and (SELECT ...) still crashes if I move (SELECT ...) to within the partition list.

New test case with 5.1.22 pulled today (please verify):

mysql> create table tl (s1 int) partition by list (s1) (partition p1 values in ((select count(*) from tl)));
ERROR 2013 (HY000): Lost connection to MySQL server during query

I wrote for bug#17977 "Partitions: crash if extractquery":
"
Yet another example that PARTITION BY (expression) is more trouble
than it's worth.  Disallow silliness like this, and limit support to
PARTITION BY (column-name) plus only a few **tested** functions.
"

Reggie set that (and others) to "Won't fix" and wrote:
"
This and all other bugs related to allowing expressions as partition functions
are being closed as "won't fix" and replaced with Bug #18198  	Expressions are
allowed as partition functions.
"

But there doesn't appear to be a limitation to support of
"PARTITION BY (column-name) plus only a few **tested** functions",
and there doesn't appear to be a case of "expressions are no longer
allowed as partition functions". So what was fixed?

PS: I should have said in the last comment that the version was "5.1.12-beta-debug".

Re-verified:

miguel@hegel:~/dbs/5.1> bin/mysql -uroot dbn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tl (s1 int) partition by list (s1) (partition p1 values in
    -> ((select count(*) from tl)));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

> But there doesn't appear to be a limitation to support of
> "PARTITION BY (column-name) plus only a few **tested** functions",
> and there doesn't appear to be a case of "expressions are no longer
> allowed as partition functions". So what was fixed?
Peter, the bugfix was doing roughly what you wanted - limiting the partitioning expression to a superposition of approved functions. And subquery wasn't in the list of allowed functions, so attempt to use it as such should have resulted in an error.  We'll have to check why does the problem still occur.

Sergey Petrunia's last comment was correct, the partitioning expression
now may only have "approved" functions. Clearly, there is a fix and lots
of hard work was involved. However, I had hoped for "only a few
**tested** functions". The number of approved functions is still large,
and not thoroughly tested. For example, here is yet another bug, found
today:

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t16 (s1 varchar(1) character set utf8, primary key (s1)) engine=myisam partition by list (octet_length(s1)) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t16 values ('y'),('ÿ');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t16 where s1 = 'y';
+----+
| s1 |
+----+
| y  |
+----+
1 row in set (0.01 sec)

(Since Y = Y with diaeresis, the behaviour is wrong.)

This is the only bug I found, and it took me two hours
to find. But if I can find one, then I know the testing
wasn't as thorough as what I did. To me, that is strong
evidence that the number of approved functions is too large.

The problem that still remains and which I need to fix is that the fix only checked the item
tree of the partition function and Peter had a "nasty" function :) in the partition constant
function. I need to fix this such that also they are checked properly.

With the fix it is very easy to decide on what to support and what not to support. It's merely
a one-line function per item tree variant.

It should be return 0; for supported functions and bool_arg= TRUE; return 0; for not
supported functions.

The bug Peter found was an interesting bug actually.
The problem is the following if generalised:
Here x1 and x2 are two constants that should both be treated as equal when
applying equal condition on them.

Partitioning function (x1) = 1
Partitioning function (x2) = 2

Thus although they are treated as equal they are sorted into different partitions.
Thus they are equal in some respects but not in all respects. So how does one
find all such occurrences?

Added bug #17136 as duplicate to this bug

This patch solves one of the problems described by PeterG.
Still working on the octet_length problem

Another example. Not critical since I'm doing something the manual tells me to avoid.

mysql> create table tsq (s1 date) partition by range (to_days(s1)) (partition p1 values less than (365243), partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tsq values ('0001-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tsq where s1 < '1000-01-01';
Empty set (0.00 sec)

Another example.

mysql> create table te (s1 varchar(5), key(s1)) partition by list (ascii(substri                                                                           ng(s1,2,1))) (partition p0 values in (0), partition p32 values in (32), partitio                                                                           n p97 values in (97), partition p98 values in (98), partition p99 values in (99)                                                                           );
Query OK, 0 rows affected (0.18 sec)

mysql> insert into te values ('a'),('a '),('aa'),(0x6100);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from te where s1 = 'a';
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.01 sec)

Had some discussions with Sergey Petrunia when reviewing it.
Our basic idea for long-term solution of this problem is as follows.
1) For all CHAR/VARCHAR fields do the following:
Before calling get_partition_id (function to determine the partition to place data in)
one calls a function that converts the string to a standardised form. For binary collations
and CHAR's this actually is an empty function. For binary collations and VARCHAR's it is
a space-spadded string (padded to max length of VARCHAR).
For single character collation it probably requires things like cast to lower case and potentially some
more.
For multicharacter collations it is possible to cast it into standard format.
The problem however is that the functions on the multibyte character standard format will mostly
be rubbish, e.g. what is substring of a standard formed multicharacter byte string.

So what will be done is that we will allow lots of functions, however many of the string functions
will only be allowed if used with fields in allowed character sets. So trying to use CHAR/VARCHAR
with character sets in partition functions that doesn't have a good mapping into standard format will
not be allowed.

I don't sense anything really wrong with the patch but don't feel qualified to review it completely.  It's quite involved.  If Sergey Petrunia (who was also reviewing I think) also approves, then I think it's good to go.

Patch queued in 5.1-kt tree

I have devised some test cases which show more failures.

TEST CASE #1
------------

If I partition by the ordinal of the reverse of the concatenation
of two columns, and then I use both columns in a WHERE clause
(ANDed), I fail to retrieve the right values.

mysql> create table t15 (s1 char(3), s2 varchar(3))
    ->  partition by list( ord(reverse(concat(s1,s2))))
    ->  (partition p1 values in (32),
    ->   partition p2 values in (65),
    ->   partition p3 values in (165));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t15 values ('a','a'),('','A'),('a ','A ');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t15 values (' a',' A'),('aaa','   '),(' A','');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t15 where s1 = 'a' and s2 = 'a';
Empty set (0.00 sec)

TEST CASE #2
------------

If I use ASCII()+MOD in the partition function, and use ASCII() in the
subpartition function, I fail to find what I've just inserted.

mysql> create table t8 (s1 char(5) character set koi8r)
    ->  partition by list (ascii(s1) mod 3) subpartition by hash(ascii(s1))
    ->  (partition p1 values in (1) (subpartition s1, subpartition s2, subpartition s3),
    ->   partition p2 values in (2) (subpartition s4, subpartition s5, subpartition s6),
    ->   partition p0 values in (0) (subpartition s7, subpartition s8, subpartition s9));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t8 values ('a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t8 where s1 = 'a';
Empty set (0.00 sec)

TEST CASE #3
------------

I get warnings when I insert, presumably caused by CAST.
The warnings are about "truncation"; there's no truncation.
The warnings don't occur if there's no partitioning.

mysql> create table t7 (s1 decimal(65,30))
    ->  partition by range (cast(s1*2 as signed integer))
    ->  (partition p1 values less than maxvalue);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (9223372036854776000);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

TEST CASE #4
------------

I can use ASCII() or ORD() on 8-bit character sets only.

mysql> create table t8 (s1 char(5) character set cp1251)
    ->  partition by list (ord(s1)) (partition pn values in (null));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t9 (s1 char(5) character set big5)
    ->  partition by list (ord(s1)) (partition pn values in (null));
ERROR 1552 (HY000): This partition function is not allowed

TEST CASE #5
------------

The octet_length() function tries to give the defined length,
for a varchar. It should give the actual length.

mysql> create table t14 (s1 varchar(3072))
    ->  partition by list(octet_length(s1)) (partition p1 values in (0));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t14 values ('');
ERROR 1513 (HY000): Table has no partition for value 3072

TEST CASE #6
------------

The example that I gave on June 20 (see earlier comments) is still failing.

And here is another test case, which shows that WEEK() won't work.

TEST CASE #7
------------

set default_week_format = 0;
create table tw (s1 date) partition by list (week(s1))
     (partition p1 values in (0), partition p2 values in (52));
insert into tw values ('2005-01-01');
set default_week_format = 7;
insert into tw values ('2005-01-01');
select * from tw where s1 = '2005-01-01';
alter table tw drop partition p1;
select * from tw where s1 = '2005-01-01';
select week('2005-01-01');
insert into tw values ('2005-01-01');
select * from tw;
select * from tw where s1 = '2005-01-01';
show create table tw;
alter table tw add partition (partition p1 values in (0));
set default_week_format = 0;
insert into tw values ('2005-01-01');
insert into tw values ('2005-01-01');
insert into tw values ('2005-01-01');
select * from tw where s1 = '2005-01-01';
select * from tw;

The final two SELECTs from the above script will produce this:

mysql> select * from tw where s1 = '2005-01-01';
+------------+
| s1         |
+------------+
| 2005-01-01 |
| 2005-01-01 |
| 2005-01-01 |
+------------+
3 rows in set (0.11 sec)

mysql> select * from tw;
+------------+
| s1         |
+------------+
| 2005-01-01 |
| 2005-01-01 |
| 2005-01-01 |
| 2005-01-01 |
+------------+
4 rows in set (0.21 sec)

And here is another test case, which shows that NOT won't work.

And here is another test case, which shows that WEEK() won't work.

TEST CASE #8
------------

mysql> set sql_mode=high_not_precedence;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ty (s1 int) partition by list (not s1 + 2) (partition pn values in (null), partition p0 values in (0), partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.03 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ty values (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

And here is another test case, which shows that division won't work.

TEST CASE #9
------------

Start mysqld with --div-precision-increment=0

CREATE TABLE tz (s1 int, key(s1))
PARTITION BY LIST (cast(round(s1/21) as signed int))
(PARTITION p3 VALUES IN (3), PARTITION p2 VALUES IN (2));

INSERT INTO tz VALUES (55);

Shut down.
Start mysqld again, with --div-precision-increment=2

INSERT INTO tz VALUES (55);
SELECT * FROM tz WHERE s1 = 55;
SELECT * FROM tz;

Result of the final two SELECTs will look like this:

mysql> select * from tz where s1 = 55;
+------+
| s1   |
+------+
|   55 |
+------+
1 row in set (0.00 sec)

mysql> select * from tz;
+------+
| s1   |
+------+
|   55 |
|   55 |
+------+
2 rows in set (0.00 sec)

I have just noticed that "test case #8" is more serious
than I indicated earlier. If I create that in database
X, and later (after starting mysqld with different
settings) say "use X", crash. So not only does it crash,
it causes crashes later.

And here is another test case.

TEST CASE #10
-------------

/* Start mysqld. */
/* Start mysql client. */

create database d50;

use d50;

set sql_mode=ignore_space;

create table tn (s1 date) partition by list (to_days (s1)) (partition p1 values in (732974));

quit

/* Take down the mysqld server. Start mysqld again. */
/* Start mysql client. */
/* Now sql_mode='', the default. */

use d50;

/* At this point, mysqld crashes. */

And here is another test case.

TEST CASE #11
-------------

mysql> create table td (s1 date) partition by list(ascii(dayname(s1))) (partition p1 values in (68),partition p2 values in (106));
Query OK, 0 rows affected (0.00 sec)

mysql> set lc_time_names='fr_FR';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into td values ('2004-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> set lc_time_names='de_DE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from td where s1 = '2004-01-01';
Empty set (0.00 sec)

And here is another test case.

TEST CASE #12
-------------

mysql> create table tp4 (s1 char(1)) partition by list(ascii(s1)) (partition p1 values in (ascii('i')));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tp4 values ('i');
ERROR 1513 (HY000): Table has no partition for value 73

And here is another test case.

TEST CASE #13
-------------

mysql> create table tn (s1 char(5) character set latin5)
    -> partition by list ((s1 = 'i'))
    -> (partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tn values ('İ');
ERROR 1513 (HY000): Table has no partition for value 0

And here is another test case.

TEST CASE #14
-------------

mysql> create table tp4 (s1 date) partition by list((s1 in(1,2))) (partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)

At this point, mysqld has crashed.

Re-verified with TEST CASE #14.

Robin Schumacher and I have determined what should (and should not) be legal 
in a CREATE TABLE partition clause. The limitations are as follows:

- No subqueries.

- No nested functions; that is, nothing like
PARTITION BY LIST (func1(func2(column_name)))
This still leaves a large range of possibilities, e.g.
func1(column_name) + func1(column_name) + func2(column_name)
is fine; that is, having more than one arithmetic operator 
to link expressions is okay.

- No use of CASE.

- No calls to stored procedures.

- No executions of stored functions or UDFs (user-defined functions).

- No use of plug-ins.

- No use of declared variables or @variables, or
any other item that is non-deterministic; that is,
nothing that can change value between the time one
does CREATE TABLE and the time the partition is
used, implicitly, because of a data-change.

- Any use of a function (e.g. ASCII/ORD) to convert
a string value (e.g. CHAR/VARCHAR) to an integer will
work only when the string has an 8-bit character set.
The collation used for the string can be any collation
for the related character set, with the exception of
latin1_german2_ci, latin2_czech_cs, cp1250_czech_cs,
all of which require a one-to-many character conversion.

- Only the following functions are to be supported 
(all others will be blocked):
ABS, ASCII, CEILING, DAY, DAYOFMONTH, DAYOFWEEK, 
DAYOFYEAR, EXTRACT, FLOOR, HOUR, MICROSECOND, 
MINUTE, MOD, MONTH, ORD, QUARTER, SECOND, 
TIME_TO_SEC, TO_DAYS, WEEKDAY, WEEKOFYEAR, YEAR, 
YEARWEEK.

- Basic arithmetic operations -- using + - * / -- will
be supported in the PARTITION clause; the result of the
calculation must be an integer, though, or results are
not guaranteed.

Further to my last comment, the following functions are currently
accepted in a CREATE TABLE ... PARTITION clause and should not be.
The use of any of the following should result in the failure of 
the CREATE TABLE statement with:
ERROR 1552 (HY000): This partition function is not allowed

List of functions that need to be blocked in partitioning:
GREATEST, ISNULL, LEAST, CASE, IFNULL, NULLIF, BIT_LENGTH,
CHAR_LENGTH, CHARACTER_LENGTH, FIND_IN_SET, INSTR, LENGTH,
LOCATE, OCTET_LENGTH, POSITION, STRCMP, CRC32, ROUND,
SIGN, DATEDIFF, PERIOD_ADD, PERIOD_DIFF, TIMESTAMPDIFF.
UNIX_TIMESTAMP, WEEK, CAST, CONVERT, the bit functions
{ | and & and ^ and << and >> and ~ }, BIT_COUNT and
INET_ATON.

Test cases for the above:
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,10;
drop table if exists t11,t12,t13,t14,t15,t16,t17,t18,t19,20;
drop table if exists t21,t22,t23,t24,t25,t26,t27,t28,t29,30;
drop table if exists t31,t32,t33,t34,t35,t36,t37,t38,t39,40;
drop table if exists t41,t42,t43;
create table t1 (col1 int) partition by range(greatest(col1,10)) (partition p0 values less than (2), partition p1 values less than (6));
create table t2 (col1 int) partition by range(isnull(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t3 (col1 int) partition by range(least(col1,12)) (partition p0 values less than (2), partition p1 values less than (6));
create table t4 (col1 int) partition by range(case when col1>0 then 10 else 20 end) (partition p0 values less than (2), partition p1 values less than (6));
create table t5 (col1 int) partition by range(ifnull(col1,5)) (partition p0 values less than (2), partition p1 values less than (6));
create table t6 (col1 int) partition by range(nullif(col1,5)) (partition p0 values less than (2), partition p1 values less than (6));
create table t7 (col1 int) partition by range(bit_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t8 (col1 char(5)) partition by range(bit_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t9 (col1 int) partition by range(char_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t10 (col1 char(5)) partition by range(char_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t11 (col1 char(5)) partition by range(character_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t12 (col1 int) partition by range(find_in_set(col1,1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t13 (col1 char(5)) partition by range(find_in_set(col1,'1')) (partition p0 values less than (2), partition p1 values less than (6));
create table t14 (col1 int) partition by range(instr(col1,3)) (partition p0 values less than (2), partition p1 values less than (6));
create table t15 (col1 char(5)) partition by range(instr(col1,'3')) (partition p0 values less than (2), partition p1 values less than (6));
create table t16 (col1 char(5)) partition by range(length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t17 (col1 char(5)) partition by range(locate(1,col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t18 (col1 int) partition by range(locate(1,col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t19 (col1 char(5)) partition by range(octet_length(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t20 (col1 char(5)) partition by range(position(1 in col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t21 (col1 int) partition by range(position(1 in col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t22 (col1 char(5)) partition by range(strcmp(col1,2)) (partition p0 values less than (2), partition p1 values less than (6));
create table t23 (col1 int) partition by range(strcmp(col1,2)) (partition p0 values less than (2), partition p1 values less than (6));
create table t24 (col1 int) partition by range(crc32(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t25 (col1 int) partition by range(round(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t26 (col1 int) partition by range(sign(col1)) (partition p0 values less than (2), partition p1 values less than (6));
create table t27 (col1 datetime) partition by range(datediff(col1,col1)) (partition p0 values less than (10), partition p1 values less than (30));
create table t28 (col1 datetime) partition by range(period_add(col1,5)) (partition p0 values less than (10), partition p1 values less than (30));
create table t29 (col1 datetime, col2 datetime) partition by range(period_diff(col1,col2)) (partition p0 values less than (10), partition p1 values less than (30));
create table t30 (col1 int, col2 int) partition by range(period_diff(col1,col2)) (partition p0 values less than (10), partition p1 values less than (30));
create table t31 (col1 datetime) partition by range(timestampdiff(day,5,col1)) (partition p0 values less than (10), partition p1 values less than (30));
create table t32 (col1 date) partition by range(unix_timestamp(col1)) (partition p0 values less than (10), partition p1 values less than (30));
create table t33 (col1 datetime) partition by range(week(col1)) (partition p0 values less than (10), partition p1 values less than (30));
create table t34 (col1 varchar(25)) partition by range(cast(col1 as signed)) (partition p0 values less than (10), partition p1 values less than (30));
create table t35 (col1 varchar(25)) partition by range(convert(col1,unsigned)) (partition p0 values less than (10), partition p1 values less than (30));
create table t36 (col1 int) partition by range(col1 | 20) (partition p0 values less than (10), partition p1 values less than (30));
create table t37 (col1 int) partition by range(col1 & 20) (partition p0 values less than (10), partition p1 values less than (30));
create table t38 (col1 int) partition by range(col1 ^ 20) (partition p0 values less than (10), partition p1 values less than (30));
create table t39 (col1 int) partition by range(col1 << 20) (partition p0 values less than (10), partition p1 values less than (30));
create table t40 (col1 int) partition by range(col1 >> 20) (partition p0 values less than (10), partition p1 values less than (30));
create table t41 (col1 int) partition by range(~col1) (partition p0 values less than (10), partition p1 values less than (30));
create table t42 (col1 int) partition by range(bit_count(col1)) (partition p0 values less than (10), partition p1 values less than (30));
create table t43 (col1 int) partition by range(inet_aton(col1)) (partition p0 values less than (10), partition p1 values less than (30));

show tables;
-- correct result is no tables

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,10;
drop table if exists t11,t12,t13,t14,t15,t16,t17,t18,t19,20;
drop table if exists t21,t22,t23,t24,t25,t26,t27,t28,t29,30;
drop table if exists t31,t32,t33,t34,t35,t36,t37,t38,t39,40;
drop table if exists t41,t42,t43;

I have an additional test case.
When changing SQL mode on a table partitioned using the MINUS operator (-), the data is partitioned differently, and removing a partition leads to different results.

 
How to repeat:

set sql_mode = '';
drop table if exists t1, t2;

create table t1 (
    x int unsigned not null,
    y int unsigned not null,
    primary key (x,y))
partition by range (x - y)
(
    partition p01 values less than (0),
    partition p02 values less than (10000),
    partition p03 values less than (MAXVALUE)
);

create table t2 like t1;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
insert into t1 values (1,2), (3,4), (5,4),(6,5);
insert into t2 select * from t1;

set sql_mode = '';
insert into t1 values (1000,2000), (3000,4000), (5000,4000),(6000,5000);
insert into t2 select * from t1 where x >= 1000;
select x,y, x-y from t1;
select x,y, x-y from t2;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
select x,y, x-y from t1;
select x,y, x-y from t2;

alter table t1 drop partition p01;

set sql_mode = '';
alter table t2 drop partition p01;
select count(*) from t1;
select count(*) from t2;
select x,y, x-y from t1;
select x,y, x-y from t2;

drop table t1,t2;

# ---------------
Here is a sample run:
mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.01 sec)

mysql> select x,y, x-y from t1;
+------+------+-------+
| x    | y    | x-y   |
+------+------+-------+
|    1 |    2 |    -1 |
|    3 |    4 |    -1 |
|    5 |    4 |     1 |
|    6 |    5 |     1 |
| 1000 | 2000 | -1000 |
| 3000 | 4000 | -1000 |
| 5000 | 4000 |  1000 |
| 6000 | 5000 |  1000 |
+------+------+-------+
8 rows in set (0.00 sec)

mysql> select x,y, x-y from t2;
+------+------+-------+
| x    | y    | x-y   |
+------+------+-------+
|    1 |    2 |    -1 |
|    3 |    4 |    -1 |
|    5 |    4 |     1 |
|    6 |    5 |     1 |
| 1000 | 2000 | -1000 |
| 3000 | 4000 | -1000 |
| 5000 | 4000 |  1000 |
| 6000 | 5000 |  1000 |
+------+------+-------+
8 rows in set (0.00 sec)

mysql>
mysql> alter table t1 drop partition p01;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t2 drop partition p01;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select x,y, x-y from t1;
+------+------+----------------------+
| x    | y    | x-y                  |
+------+------+----------------------+
|    1 |    2 | 18446744073709551615 |
|    3 |    4 | 18446744073709551615 |
|    5 |    4 |                    1 |
|    6 |    5 |                    1 |
| 1000 | 2000 | 18446744073709550616 |
| 3000 | 4000 | 18446744073709550616 |
| 5000 | 4000 |                 1000 |
| 6000 | 5000 |                 1000 |
+------+------+----------------------+
8 rows in set (0.00 sec)

mysql> select x,y, x-y from t2;
+------+------+------+
| x    | y    | x-y  |
+------+------+------+
|    5 |    4 |    1 |
|    6 |    5 |    1 |
| 5000 | 4000 | 1000 |
| 6000 | 5000 | 1000 |
+------+------+------+
4 rows in set (0.00 sec)

In light of the limitations already defined
(Trudy & Robin, 31 Oct 2006),
and in light of the issues related to SQL_MODE found during testing,
I am proposing to add the following limitations :

SQL_MODE REAL_AS_FLOAT must be set
SQL_MODE PIPES_AS_CONCAT must be set
SQL_MODE ANSI_QUOTES must be set
SQL_MODE IGNORE_SPACE must be set
SQL_MODE ONLY_FULL_GROUP_BY must *not* be set
SQL_MODE NO_UNSIGNED_SUBSTRACTION must *not* be set
SQL_MODE NO_DIR_IN_CREATE must *not* be set
SQL_MODE NO_AUTO_VALUE_ON_ZERO must *not* be set
SQL_MODE NO_BACKSLASH_ESCAPES must *not* be set
SQL_MODE NO_ZERO_IN_DATE must *not* be set
SQL_MODE NO_ZERO_DATE must *not* be set
SQL_MODE INVALID_DATES must *not* be set
SQL_MODE ERROR_FOR_DIVISION_BY_ZERO must *not* be set
SQL_MODE HIGH_NOT_PRECEDENCE must *not* be set

If any of these limitations is not met, both :
- creating a partition
- using a partition
should fail with an error.

The rational is that the modes listed can affect:
- the table creation
- the parsing of an expression
- the runtime result of an expression
so that if an expression affected by any of these modes is used
to partition the data, the expression evaluation can not be guaranteed
to point always to the same partition.

Put it another way :
the modes listed are to support backward compatibility of existing applications
written in 3.x, 4.0, 4.1, 5.0 ... with the 5.1 release.
There is no expectation the a *new* feature (partitions) honor these backward
compatibility modes, and in fact, due to the ambiguity in expression
evaluation that these modes introduces, there is both no technical solution
to support that, and no business case (in my understanding) in doing so.

If someone what to use partitioning, they *have* to migrate their application
to SQL_MODE=ANSI.

Just a proposal, documenting it in the related bug report.
Feel free to comment.

Regarding Marc Alff's suggestion:
I think we should consider this suggestion -- but only after the current
work needed to fix bug#18198 is done; let's stick to one problem at a 
time.

But I see no reason to insist that partitioning fail because the user
also wants to ensure that an application will accept only valid dates;
NO_ZERO_DATE and NO_ZERO_IN_DATE, for example, are part of 'strict' mode. 
It's generally the Enterprise user who will benefit from partitioning 
and who will also want MySQL to do strict type-checking.

ASCII( UCASE() ) failed in 5.1.16-bk; works again in 5.1.17-bk, also works in latest Telco (5.1.6-ndb-6.2.0-bk) -- see Bug #26082.

And here is another test case.

TEST CASE #15
-------------

mysql> create table t15 (s1 varchar(1), primary key (s1))
    -> partition by list (ascii(s1))
    -> (partition p1 values in (65));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t15 values ('A');
Query OK, 1 row affected (0.01 sec)

mysql> replace into t15 values ('A');
ERROR 1032 (HY000): Can't find record in 't15'

See also Bug #29308.

Patch pushed to 5.1-opt

Pushed into 5.1.21-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.21 changelog.

Just a quick comment for anyone browsing.  ASCII and ORD are NOT allowed as partition functions.  Please see Bug #30189 for details.

see related bug #39882

marked bug#26063 as a duplicate of this.