Bug #30188 Partitions: I can use DIV in a function
Submitted: 1 Aug 2007 21:30 Modified: 12 Dec 2007 17:33
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.21-beta-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Jon Stephens CPU Architecture:Any
Tags: div, partition, partitioning

[1 Aug 2007 21:30] Peter Gulutzan
The MySQL Reference Manual does not mention DIV among the
valid functions or arithmetic operators. Therefore it
should be illegal.

For division, the MySQL Reference does mention "/" for division:
"Use of the arithmetic operators +, –, ×, and / is permitted
in partitioning expressions ...". But I'm unable to see how I
could use "/".

How to repeat:
mysql> create table t18 (s1 int) partition by list (0 div s1) (partition p1 values in (null));
Query OK, 0 rows affected (0.06 sec)
[1 Aug 2007 22:28] MySQL Verification Team
Thank you for the bug report. Verified as described.
[15 Oct 2007 15:08] Peter Gulutzan
Another test case:

mysql> create table t (s1 int) partition by list( (s1/3)*10 div 1) (partition p0 values in (0), partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> set @@div_precision_increment=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> set @@div_precision_increment=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create index i on t (s1);
ERROR 1525 (HY000): Table has no partition for value 3
[30 Oct 2007 22:29] John David Duncan
In Peter's example above, the problem is /, not DIV.  Changing div_precision_increment changes the result  of s1/3. 

If the result of a partition expression must be an integer, then it seems DIV should be allowed, but / should be disallowed.
[31 Oct 2007 15:53] Peter Gulutzan
I believe my example shows a problem with DIV, I do not know a way
to get a problem using '/' withour DIV. An interesting thing about DIV
is that I can use it to change a non-integer to an integer.
(That's also true for bit functions like '|' which are no longer
legal in partition functions.)
[12 Dec 2007 17:33] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Done as part of update to Partitioning Limitations section of 5.1/6.0 Manual in which the "prohibited functions" section was removed - for now, at least, only *supported* functions for partitioning expressions will be listed in the Manual.
[13 Dec 2007 13:59] Mattias Jonsson
see Bug#33182 the '/' operator will be disallowed as partitioning function, due to it can change behavior with div_precision_increment. the DIV operator will still be supported.