MySQL Bugs: #52815: LIST COLUMNS doesn't insert rows in correct partition if muliple columns used

Bug #52815	LIST COLUMNS doesn't insert rows in correct partition if muliple columns used
Submitted:	14 Apr 2010 10:29	Modified:	6 Dec 2010 7:22
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5+	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
If multiple column used in LIST PARTITON expression mysqld inserts rows in not correct partition.

How to repeat:
mysql> drop table employees_country;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE employees_country (
    -> id INT NOT NULL,
    -> name VARCHAR(255),
    -> department VARCHAR(10),
    -> country VARCHAR(255)
    -> ) PARTITION BY LIST COLUMNS (department, country) (
    -> PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
    -> PARTITION second_office VALUES IN (('dep2', 'Russia'))
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(1, 'Ann', 'dep1', 'Russia');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(2, 'Bob', 'dep1', 'Croatia');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(3, 'Cecil', 'dep2', 'Russia');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(3, 'Dan', 'dep2', 'Croatia');
ERROR 1526 (HY000): Table has no partition for value from column_list
mysql> 
mysql> SELECT PARTITION_NAME,TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME = 'employees_country';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| first_office   |          3 |
| second_office  |          0 |
+----------------+------------+
2 rows in set (0.00 sec)

Can you please try to create a partition for 'dep2', 'Croatia' and try again?

PARTITION BY LIST COLUMNS (department, country) (
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION second_office VALUES IN (('dep2', 'Russia')));

mysql> INSERT INTO employees_country VALUES(3, 'Dan', 'dep2', 'Croatia');
ERROR 1526 (HY000): Table has no partition for value from column_list
mysql>

Same result:

mysql> drop table if exists employees_country;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE employees_country (
    -> id INT NOT NULL,
    -> name VARCHAR(255),
    -> department VARCHAR(10),
    -> country VARCHAR(255)
    -> ) PARTITION BY LIST COLUMNS (department, country) (
    -> PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
    -> PARTITION second_office VALUES IN (('dep2', 'Russia')),
    -> PARTITION third_office VALUES IN (('dep2', 'Croatia'))
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> INSERT INTO employees_country VALUES(1, 'Ann', 'dep1', 'Russia');
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(2, 'Bob', 'dep1', 'Croatia');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(3, 'Cecil', 'dep2', 'Russia');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO employees_country VALUES(3, 'Dan', 'dep2', 'Croatia');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT PARTITION_NAME,TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME = 'employees_country';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| first_office   |          4 |
| second_office  |          0 |
| third_office   |          0 |
+----------------+------------+
3 rows in set (0.18 sec)

Repeatable in mysql-trunk-bugfixing

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

3016 Mattias Jonsson	2010-04-21
      Bug#52815: LIST COLUMNS doesn't insert rows in correct
                 partition if muliple columns used
      
      Problem was that range scanning through the sorted array of
      the column list values did not use a correct index calculation.
      
      Fixed by also taking the number of columns in the calculation.
     @ mysql-test/r/partition_column.result
        Bug#52815: LIST COLUMNS doesn't insert rows in correct
                   partition if muliple columns used
        
        Added test result for bug.
     @ mysql-test/t/partition_column.test
        Bug#52815: LIST COLUMNS doesn't insert rows in correct
                   partition if muliple columns used
        
        Added test for bug.
     @ sql/sql_partition.cc
        Bug#52815: LIST COLUMNS doesn't insert rows in correct
                   partition if muliple columns used
        
        list_col_array is a matrix of field values, so one must also
        use the number of columns to calculate the correct partition
        id.

pushed into mysql-trunk-bugfixing, mysql-next-mr-bugfixing and mysql-6.0-codebase-bugfixing

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Documented fix in the 5.5.5 changelog as follows:

        Rows inserted into a table created using a PARTITION BY LIST 
        COLUMNS option referencing multiple columns could be inserted 
        into the wrong partition.

Set to Need Merge, waiting for addition merges/version #s.

Mattias confirms this is already in -trunk. No need for a 5.6 changelog entry. Closed.