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
Tags: documentation, partition, qc
Triage: D4 (Minor)

[22 Jun 2007 21:01] Martin Friebe
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:
-
[23 Jun 2007 7:20] Valerii Kravchuk
Thank you for a documentation request. That page surely should be updated.
[23 Jun 2007 20:05] Jon Stephens
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.
[24 Jun 2007 5:11] Valerii Kravchuk
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.
[24 Jun 2007 6:38] Jon Stephens
> 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.
[3 Jul 2007 23:58] Trudy Pelzer
A patch for bug#18198, which should solve these
problems, was pushed on July 2nd.
[22 Aug 2007 18:09] Timothy Smith
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
[23 Aug 2007 9:54] Jon Stephens
Please remember to put Docs bugs in the proper category, otherwise the Docs Team might never know about them - thanks!
[23 Aug 2007 9:55] Jon Stephens
Assigned to myself, with Stefan as lead.
[23 Aug 2007 13:51] 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.