| Bug #71095 | Wrong results with PARTITION BY LIST COLUMNS() | ||
|---|---|---|---|
| Submitted: | 5 Dec 2013 16:00 | Modified: | 16 May 2014 13:51 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.5.33, 5.6.14, 5.6.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Optimizer, partition by list columns, partitioning, regression | ||
[6 Dec 2013 11:06]
MySQL Verification Team
Hello Valeriy, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[6 Dec 2013 11:07]
MySQL Verification Team
// 5.6.15 mysql> select * from tp; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | | 2 | 3 | 1 | 1 | +----+----+------+------+ 2 rows in set (0.00 sec) mysql> select * from tp where c1 = 1; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1 = 1 and c2=1; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1=2 and c2=3; Empty set (0.00 sec) mysql> select * from tp where c1=2; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 2 | 3 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> explain partitions select * from tp where c1=2; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | tp | p1,p2 | ref | PRIMARY | PRIMARY | 4 | const | 2 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> explain partitions select * from tp where c1=2 and c2=3; +----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec)
[6 Dec 2013 13:45]
Mattias Jonsson
Yes, looks like a regression. The workaround for this specific CREATE TABLE is to remove the COLUMNS, since it is not necessary for single int columns and use old 5.1 LIST partitioning: create table tp(c1 int, c2 int, c3 int, c4 int, primary key(c1,c2)) engine=InnoDB partition by list (c2) (partition p1 values in (1,2) engine=InnoDB, partition p2 values in (3,4) engine=InnoDB);
[6 Dec 2013 17:11]
Mattias Jonsson
Hi Valeriy,
It was a typo in the code :(
Since it seems urgent to you, can you please test this patch (done for 5.5)?
The typo was 'num_parts' instead of 'num_list_values'. The rest of the patch is to keep the same level of pruning, not including non-used partitions.
=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc
+++ sql/sql_partition.cc
@@ -3333,10 +3333,15 @@ uint32 get_partition_id_cols_list_for_en
nparts, left_endpoint,
include_endpoint)));
- if (!left_endpoint)
+ if (!left_endpoint && list_index < part_info->num_list_values)
{
- /* Set the end after this list tuple if not already after the last. */
- if (list_index < part_info->num_parts)
+ /*
+ Set the end after this list tuple if it is not already after the last
+ and it matches.
+ */
+ int cmp = cmp_rec_and_tuple_prune(list_col_array + list_index*num_columns,
+ nparts, left_endpoint, include_endpoint);
+ if (cmp >= 0)
list_index++;
}
[6 Dec 2013 17:48]
Valeriy Kravchuk
Thank you for the patch, Mattias. I'll try to build 5.5.35 with it and check results tomorrow or on Monday.
[7 Dec 2013 16:02]
Valeriy Kravchuk
Yes, with this patch I get correct results:
openxs@ao756:~/dbs/5.5m$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.35-debug MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table tp(c1 int, c2 int, c3 int, c4 int,
-> primary key(c1,c2)) engine=InnoDB
-> partition by list columns(c2)
-> (partition p1 values in (1,2) engine=InnoDB,
-> partition p2 values in (3,4) engine=InnoDB);
select * from tp where c1=2 and c2=3;Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> insert into tp values (1,1,1,1),(2,3,1,1);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tp;
+----+----+------+------+
| c1 | c2 | c3 | c4 |
+----+----+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 3 | 1 | 1 |
+----+----+------+------+
2 rows in set (0.00 sec)
mysql> select * from tp where c1=2 and c2=3;
+----+----+------+------+
| c1 | c2 | c3 | c4 |
+----+----+------+------+
| 2 | 3 | 1 | 1 |
+----+----+------+------+
1 row in set (0.01 sec)
Thank you for fixings this fast. Probably workarounds (different partitioning) will be used till the official release anyway.
[7 Jan 2014 9:32]
steve steve
I can't believe that mysql only mark this bug as non-critical, this bug makes our partition database unusable, the only solution for me is downgrade mysql and re import old database backup (because data is become uncorrected, such as update xxx where c1=xx and c2=xxx, this update sql will fail for updating real data). It's lucky that my database is only slave.
[16 May 2014 13:51]
Jon Stephens
Fixed in MySQL 5.5.39, 5.6.20, 5.7.5. Documented fix as follows in the corresponding changelogs:
Selecting from a table having multiple columns in its primary
key and partitioned by LIST COLUMNS(R) where R was the last
(rightmost) column listed in the primary key definition returned
an incorrect result.
[18 May 2014 23:02]
Luciano C
Was the fix applied the one that is on this report on another comment? It doesn't work for me. Where can i find the applied patch? I can't on bzr.
[1 Aug 2014 15:52]
Laurynas Biveinis
5.5 $ bzr log -r 4633 ------------------------------------------------------------ revno: 4633 committer: Mattias Jonsson <mattias.jonsson@oracle.com> branch nick: b17909699-55_2 timestamp: Tue 2014-05-06 11:05:37 +0200 message: Bug#17909699: WRONG RESULTS WITH PARTITION BY LIST COLUMNS() Typo leading to not including the last list values (partition). Also improved pruning to skip last partition if not used. rb#4762 approved by Aditya and Marko.
Description: One may get wrong results with recent MySQL 5.5.x and 5.6x versions why selecting from the table having multiple column primary key and partitioned by list columns() on second column for the primary key: mysql> create table tp(c1 int, c2 int, c3 int, c4 int, -> primary key(c1,c2)) engine=InnoDB -> partition by list columns(c2) -> (partition p1 values in (1,2) engine=InnoDB, -> partition p2 values in (3,4) engine=InnoDB); Query OK, 0 rows affected (0.90 sec) mysql> insert into tp values (1,1,1,1),(2,3,1,1); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tp; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | | 2 | 3 | 1 | 1 | +----+----+------+------+ 2 rows in set (0.00 sec) mysql> select * from tp where c1 = 1; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1 = 1 and c2=1; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1=2 and c2=3; Empty set (0.00 sec) mysql> select * from tp where c1=2; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 2 | 3 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> explain partitions select * from tp where c1=2; +----+-------------+-------+------------+------+---------------+---------+------ ---+-------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_l en | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+---------+------ ---+-------+------+-------+ | 1 | SIMPLE | tp | p1,p2 | ref | PRIMARY | PRIMARY | 4 | const | 2 | NULL | +----+-------------+-------+------------+------+---------------+---------+------ ---+-------+------+-------+ 1 row in set (0.00 sec) mysql> explain partitions select * from tp where c1=2 and c2=3; +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.14-log | +------------+ 1 row in set (0.00 sec) I have reasons to think that in older versions (like 5.5.21) this query returned correct results, so this is a regression bug. How to repeat: create table tp(c1 int, c2 int, c3 int, c4 int, primary key(c1,c2)) engine=InnoDB partition by list columns(c2) (partition p1 values in (1,2) engine=InnoDB, partition p2 values in (3,4) engine=InnoDB); insert into tp values (1,1,1,1),(2,3,1,1); select * from tp; select * from tp where c1=2 and c2=3; select * from tp where c1=2; explain partitions select * from tp where c1=2; explain partitions select * from tp where c1=2 and c2=3; Have fun explaining the results. Suggested fix: Make sure to do at least basic testing to prevent regression bugs? There is a workaround it seems, use separate partition for each value of c2: mysql> drop table tp; Query OK, 0 rows affected (0.28 sec) mysql> create table tp(c1 int, c2 int, c3 int, c4 int, -> primary key(c1,c2)) engine=InnoDB -> partition by list columns(c2) -> (partition p1 values in (1) engine=InnoDB, -> partition p2 values in (2) engine=InnoDB, -> partition p3 values in (3) engine=InnoDB, -> partition p4 values in (4) engine=InnoDB); Query OK, 0 rows affected (1.80 sec) mysql> insert into tp values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4); Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from tp where c1=1 and c2=1; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 1 | 1 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1=2 and c2=2; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 2 | 2 | 2 | 2 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1=3 and c2=3; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 3 | 3 | 3 | 3 | +----+----+------+------+ 1 row in set (0.00 sec) mysql> select * from tp where c1=4 and c2=4; +----+----+------+------+ | c1 | c2 | c3 | c4 | +----+----+------+------+ | 4 | 4 | 4 | 4 | +----+----+------+------+ 1 row in set (0.00 sec)