MySQL Bugs: #26082: UCASE() function, nested function call allowed in partitioning expression

Bug #26082	UCASE() function, nested function call allowed in partitioning expression
Submitted:	5 Feb 2007 13:00	Modified:	11 Jul 2007 9:10
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1-bk, telco-bk	OS:	Linux (Linux)
Assigned to:	Mikael Ronström	CPU Architecture:	Any
Tags:	functions, partitioning

Description:
"PARTITION BY LIST(ASCII( UCASE(job_code) ))" example at the page http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html is wrong

How to repeat:
$mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.16-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code CHAR(1),
    ->     store_id INT
    -> ) 
    -> PARTITION BY LIST(ASCII( UCASE(job_code) )) (
    ->     PARTITION management VALUES IN(68, 77, 79, 80),
    ->     PARTITION sales VALUES IN(66, 76, 83),
    ->     PARTITION technical VALUES IN(65, 69, 71, 73, 84),
    ->     PARTITION clerical VALUES IN(75, 78, 89),
    ->     PARTITION support VALUES IN(67, 70, 74, 82, 86),
    ->     PARTITION unassigned VALUES IN(NULL, 0, 32)
    -> );
ERROR 1554 (HY000): This partition function is not allowed
mysql> \q
Bye

Suggested fix:
Fix code or documentation

Hmmmm....

-----

jon@biollante:/usr/local/mysql/bin> ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.17-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code CHAR(1),
    ->     store_id INT
    -> )
    -> PARTITION BY LIST(ASCII( UCASE(job_code) )) (
    ->     PARTITION management VALUES IN(68, 77, 79, 80),
    ->     PARTITION sales VALUES IN(66, 76, 83),
    ->     PARTITION technical VALUES IN(65, 69, 71, 73, 84),
    ->     PARTITION clerical VALUES IN(75, 78, 89),
    ->     PARTITION support VALUES IN(67, 70, 74, 82, 86),
    ->     PARTITION unassigned VALUES IN(NULL, 0, 32)
    -> );
Query OK, 0 rows affected (0.11 sec)

...

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code CHAR(1),
    ->     store_id INT
    -> )
    -> PARTITION BY LIST(ASCII( UCASE(job_code) )) (
    ->     PARTITION management VALUES IN(68, 77, 79, 80),
    ->     PARTITION sales VALUES IN(66, 76, 83),
    ->     PARTITION technical VALUES IN(65, 69, 71, 73, 84),
    ->     PARTITION clerical VALUES IN(75, 78, 89),
    ->     PARTITION support VALUES IN(67, 70, 74, 82, 86),
    ->     PARTITION unassigned VALUES IN(NULL, 0, 32)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT VERSION();
+------------------------+
| VERSION()              |
+------------------------+
| 5.1.16-ndb-6.2.0-debug |
+------------------------+
1 row in set (0.00 sec)

-----

I've fixed the example in the Manual not to use UCASE() - changeset is here: http://lists.mysql.com/commits/21083. However, the fact that the CREATE TABLE works is a server bug, therefore I'm modifying the bug category accordingly.

Builds tested: 5.1.17-max-debug/SuSE 10.2/32-bit Intel, 5.1.16-ndb-6.2.0/SuSE 10.2/AMD64 - both built within the last 3-4 days from BitKeeper.

I looked at this again after discussing with PeterG and realised that the example that was in the Manual not only uses UCASE(), it also has a nested function call which should also be disallowed. Updated description to include this.

Removing myself as developer and Stefan as lead, since this is no longer a docs issue.

BUG#18198 is queued for push, this will remove the function UCASE as an allowed
function in a partition function and thus this problem can no longer appear.

BUG#18198 was pushed into 5.1.21-beta.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.21 changelog.