MySQL Bugs: #17686: Two Parition Error Messages that make no sense

Bug #17686	Two Parition Error Messages that make no sense
Submitted:	24 Feb 2006 2:41	Modified:	13 Mar 2006 16:12
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.8	OS:
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
While create a test for clusters I ran accross the following

mysqltest: At line 119: query 'CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE, PRIMARY KEY(id))ENGINE=NDB PARTITION BY RANGE (t) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946),  PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE)' failed: 1469: The PARTITION function returns the wrong type

Now there are two problems with the above create. One issue is that I failed to use YEAR(t) in the PARTITION BY RANGE (t)  and so this is where the first error message that does not make sense came from.

The error message:
The PARTITION function returns the wrong type
Shoule probably be more like:
This PARTITION function must use int or null values. Any incorrect type it being used.

The second error message that makes no sense is from this statement:

Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 119: query 'CREATE TABLE t1 (id MEDIUMINT NOT NULL PRIMARY KEY, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE)ENGINE=NDB PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946),  PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE)' failed: 1481: A PRIMARY KEY need to include all fields in the partition function
(the last lines may be the most important ones)

The error message:
PRIMARY KEY need to include all fields in the partition function

What does that mean? Well what I think is trying to be said is.
For tables with declared PRIMARY KEY(s), PRIMARY KEY(s) fields must be used  in the partition function 

How to repeat:
See above

Suggested fix:
Change message to something that actaully ** helps ** the user understand what they are doing wrong.

IMNSHO...

The error message regarding PK and partitioning expression could be better phrased as:

A PRIMARY KEY on a partitioned table must include all columns which are used in the partitioning expression.

The datatype error message should read something like this:

Wrong datatype: A RANGE or LIST partitioning expression must evaluate as an INT or NULL.

In addition, perror did not seem to know of these error numbers

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
product(s).

Additional info:

Regarding the primary <-> partitioning key issue, this has already been documented in the Partitioning By Key seciton of the Manual, so after discussing this bug with Mikael, I've closed it.