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 15:17	Modified:	9 Oct 2007 22:46
Reporter:	Mark Kubacki
Status:	To be fixed later
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.15-beta	OS:	Linux (GNU/Linux)
Assigned to:	Alexey Botchkov	Target Version:
Tags:	partition, ASCII, list

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.