MySQL Bugs: #30577: FLOOR() and CEILING() not usable as partition functions

Bug #30577	FLOOR() and CEILING() not usable as partition functions
Submitted:	22 Aug 2007 19:56	Modified:	7 Mar 2011 11:11
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	ceiling(), floor(), partitioning, partitioning expressions

Description:
The FLOOR() and CEILING() functions are explicitly allowed as partitioning functions, according to the discussion on bug #18198, and the documentation at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions-supported.html

However, they have a hybrid result type, and when checked by fix_partition_func() they show up as REAL_RESULT.  Partitioning functions must be INT_RESULT.  So, neither FLOOR() nor CEILING() can be used as a partitioning function in practice.

Since CAST() isn't an allowed partitioning function, it's not possible to force the result of FLOOR() or CEILING() to INT_RESULT.

How to repeat:
create table t1 (col1 float(7,4)) engine='INNODB'
partition by range(ceiling(col1))
(partition p0 values less than (15),
partition p1 values less than maxvalue);

I forgot to mention, this problem goes away if using an int column type.  But that isn't too useful for FLOOR() and CEILING(), I think.

MOD() behaves the same way, but it makes sense to use MOD() on a int column, and probably can be construed as not a bug.

FLOOR(INT) works. I don't see why this would be considered a bug.

According to the manual, you can't get a INT from a FLOOR or CEILING function unless its argument is an exact value numeric. 

http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_floor
"For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type."

Since partitions require an integer value, it is expected that the server rejects the table creation in this case.

You would get the same error ("The PARTITION function returns the wrong type") even if you tried to use ABS instead of FLOOR/CEILING.

IMO, this is not a bug.

Reopening this bug again, since FLOOR() and CEILING() is not disallowed as partitioning functions, and the use is for DECIMAL column type. Giuseppe was correct in his comment, but we all missed the DECIMAL type which is an exact-value numerical type, for which FLOOR() and CEILING() returns integer values. So they are not allowed for FLOAT/DOUBLE since they don't return INT type, and are not useful for INT's, but can be useful for DECIMAL. If you know any SQL_MODE or variable that changes the behavior for FLOOR(decimal_col) please let me know.

Test that succeeds in both 5.1 and 5.5:
CREATE TABLE t1 (a DECIMAL(7,2))
ENGINE = MyISAM
PARTITION BY RANGE (FLOOR(a))
(PARTITION p0 VALUES LESS THAN (0),
PARTITION p1000 VALUES LESS THAN (1001));
CREATE TABLE t2 (a DECIMAL(7,2))
ENGINE = MyISAM
PARTITION BY RANGE (CEILING(a))
(PARTITION p0 VALUES LESS THAN (0),
PARTITION p1000 VALUES LESS THAN (1001));
CREATE TABLE t3 (a DECIMAL(7,2))
ENGINE = MyISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (0),
PARTITION p1000 VALUES LESS THAN (1001));
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
INSERT INTO t1 VALUES (-1.01), (-1.99), (1), (1.01), (1.99);
INSERT INTO t2 VALUES (-1.01), (-1.99), (1), (1.01), (1.99);
INSERT INTO t1 VALUES (-0.99), (-0.01), (0), (0.01), (0.99);
INSERT INTO t2 VALUES (-0.99), (-0.01), (0), (0.01), (0.99);
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';
TABLE_NAME	PARTITION_NAME	TABLE_ROWS
t1	p0	4
t1	p1000	6
t2	p0	2
t2	p1000	8

I reopen this since I was assigned to fix bug#45717, enable or remove disabled tests related to this bug. I will remove the non supported tests and enable the supported ones.

So what to do is to change 'Each of these functions returns an integer only if it is passed an integer argument.' in:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

So that is says something like 'exact-value numerical type, like DECIMAL or INT, argument' instead of 'integer argument' (for 5.1+)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125308

3142 Mattias Jonsson	2010-11-29
      Bug#45717: A few test cases are disabled due to closed Bug#30577
      
      Removed floor(float_col) tests, enabled floor(decimal_col) tests.

Corrected status.

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.

NOTE: Commit for this new fix is at http://lists.mysql.com/commits/125570

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126740

3520 Mattias Jonsson	2010-12-14
      Bug#45717: A few test cases are disabled due to closed Bug#30577
      
      Backport from 5.5. OK from Anitha G. to push to 5.1.
      
      Removed floor(float_col) tests, enabled floor(decimal_col) tests

Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:mattias.jonsson@oracle.com-20101214120656-6kf94uo8jiymcq00) (merge vers: 5.1.55) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:mattias.jonsson@oracle.com-20101214121019-4a3xxe3eabc1n8ij) (merge vers: 5.5.8) (pib:24)

Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:mats.kindahl@oracle.com-20101215103340-a0wp0yq8t4byel1o) (merge vers: 5.6.1) (pib:24)

Status should be closed.

(Commits that triggered status change referred to test changes only.)