MySQL Bugs: #29308: documentation of disallowed functions in partition

Bug #29308	documentation of disallowed functions in partition
Submitted:	22 Jun 2007 21:01	Modified:	23 Aug 2007 13:51
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	documentation, partition, qc

Description:
see: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions-disallowed.html

There is a list of fucntions that are not allowed to be used in partitions. I tested many of them, It seems none of them gives an error.

I found that cast() is even used within the mysql tests?

Is that list still correct?

How to repeat:
see test partition_mgm for cast()

examples below for working statements, all functions are listed as prohibited:

drop table if exists tp1;
CREATE TABLE tp1 (a INT)PARTITION BY RANGE ( greatest(a,2) )
( PARTITION p0 VALUES LESS THAN (10));

drop table if exists tp1;
CREATE TABLE tp1 (a INT)PARTITION BY RANGE ( isnull(a) )
( PARTITION p0 VALUES LESS THAN (10));

drop table if exists tp1;
CREATE TABLE tp1 (a INT)PARTITION BY RANGE ( instr('a',a) )
( PARTITION p0 VALUES LESS THAN (10));

drop table if exists tp1;
CREATE TABLE tp1 (a INT)PARTITION BY RANGE ( cast(round(a/2,0) as signed) )
( PARTITION p0 VALUES LESS THAN (10));

drop table if exists tp1;
CREATE TABLE tp1 (a INT)PARTITION BY hash( cast(round(a/2,0) as signed) )
 PARTITIONS 4;

Suggested fix:
-

Thank you for a documentation request. That page surely should be updated.

My understanding is that if any of the listed functions or other constructs fail to produce an error when used in partitioning expressions, then that failure to do so is a bug.

I think, cast() is allowed by design. We need it in many real-life cases.

Anyway, let's say this is a verified bug then. Somebody just has to determine current list of functions that are OFFICIALLY allowed, and force proper error messages when any others are used.

> Somebody just has to determine current list of functions that are OFFICIALLY 
> allowed, and force proper error messages when any others are used.

But this has already been done. Please see Bug #18198, which says (among quite a lot of other things):

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

CAST, GREATEST, ISNULL, and ROUND do not appear to be in this "Allowed" list.

Bug #18198 also says,

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

As you can see, CAST is in this "Prohibited" list, as are GREATEST, ISNULL, and ROUND.

Therefore, all of the tests provided by the submitter of the current bug represent Server bugs, not Docs bugs, because an attempt to use any of the functions from the "Prohibited" list in a partitioning expression should result in failure + error.

A patch for bug#18198, which should solve these
problems, was pushed on July 2nd.

One thing to note:  DATEDIFF(d1, d2) is implemented directly as (TO_DAYS(d1) - TO_DAYS(d2)) (see Create_func_datediff::create() in item_create.cc).  Because of this, it too must be allowed as a partition function, if TO_DAYS and the minus function are allowed.

The current code does allow DATEDIFF(), and I recommend that it be documented as such.

Regards,

Timothy

Please remember to put Docs bugs in the proper category, otherwise the Docs Team might never know about them - thanks!

Assigned to myself, with Stefan as lead.

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.