MySQL Bugs: #26063: LIST partition not found for values which do not exist

Bug #26063	LIST partition not found for values which do not exist
Submitted:	4 Feb 2007 14:17	Modified:	15 Dec 2008 7:36
Reporter:	Mark Kubacki	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.15-beta	OS:	Linux (GNU/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ASCII, list, partition

Description:
If I create a partitioned table and copy values from another (identical, but non-partitioned) table into it, MySQL complains about missing partitions for values which do actually not exist.

(Same goes from LOAD INTO statements.)

(If I added that partition for ASCII code 91, it will complain about other codes such as 114 which do not exist. If I even added them, some records which ASCII codes such as 85 will be placed into that partition not made for them.)

How to repeat:
Given a table with country-codes as defined in ISO 3166 (thus EN, FR, US, DE, GB...), called "source": country_code, country_name, population

Create a partitioned copy:
CREATE TABLE `target` (
	`country_code` CHAR( 2 ) NOT NULL ,
	`country_name` VARCHAR( 60 ) NULL ,
	`population` INT NULL ,
	PRIMARY KEY (`country_code`, `something`),
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_ci
PARTITION BY LIST(ASCII(`country_code`)) (
	PARTITION p0	VALUES IN (NULL, 0, 72, 104, 74, 106, 79, 111, 81, 113, 87, 119),
	PARTITION p1	VALUES IN (86, 118, 89, 121),
	/* ASCII codes.. */
	PARTITION p6	VALUES IN (69, 101, 70, 102), /* E and F */
	/* ASCII codes.. */
	PARTITION p17	VALUES IN (85, 117), /* USA */
	PARTITION p18	VALUES IN (90, 122)
);

Copy data from source to target (that will fail!):
INSERT INTO target SELECT * FROM source;

This will yield in:
-- ERROR 1514 (HY000): Table has no partition for value 91

But, and here comes something I don't understand:
SELECT COUNT(*) FROM source WHERE ASCII(UCASE(country_code)) = 91;
-- will yield 0 !

Thank you for the report.

Verified as described on Linux using last development sources.

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/21692

ChangeSet@1.2477, 2007-03-11 17:46:15+04:00, holyfoot@mysql.com +3 -0
  Bug #26063 LIST partition not found for values which do not exist
  Failure appeared in get_part_id_charset_func_part() function
  (that was set as a part_info->get_patition_id) as it turns
  normal field values into some binary strings that supposed
  to be used for comparisons only. So that functions upon it
  (ASCII in this case) will behave inpredictable.
  set_up_partition_func_pointers() function fixed to set proper
  functions for LIST_PARTITION

I don't fully understand what the bug is. If there isn't a partition for value 91 then
it is correct to report an error if you try to load data into the table with a record
where value is 91.

The reason for the use of binary variant is to handle character sets correctly, so
the bug fix here introduces another bug and is thus not approved. I don't understand
from reading the bug report what the bug is, can someone clarify this.

Let me describe this issue by an example:
  Say you have a partitioned table for every (uppercase) letter, and you inserted some hundred of these letters (along with other data). Then, MySQL will complain about not being able to find a partition for the figure '5'!
  But, the latter does not exist in your data to be inserted.

Mikael,
There's no partition for the 91, but we don't insert 91 there.
As i said, original string is distorted when we create index string
from it and we use CHAR() of that distorted value as a partition
function.

Ok,
So what is the character set, wasn't aware that any of the supported character sets did any
weird translations. Problem is that the character set as is cannot be used either since it has
problems that two similar strings might map to different values.

Ok,
So what is the character set, wasn't aware that any of the supported character sets did any
weird translations. Problem is that the character set as is cannot be used either since it has
problems that two similar strings might map to different values.

The fix is not ok
I've analyzed what is going wrong. The problem is that the functions in the CREATE TABLE
statement is not using character set functions in the same way as the partition function
are doing. I'm still thinking of how to fix it, it is a pretty complex problem with no really
simple solution, so will discuss this with PeterG.

Duplicate of bug#18198.

(ASCII function is not longer enabled for use in partitioning function.)