MySQL Bugs: #30188: Partitions: I can use DIV in a function

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:	None
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

Description:
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 "/".
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

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)

Thank you for the bug report. Verified as described.

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

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.

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.)

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.

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.