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 23:30	Modified:	12 Dec 2007 18:33
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.1.21-beta-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Jon Stephens	Target Version:
Tags:	div, partition, partitioning
Triage:	D4 (Minor)

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.