MySQL Bugs: #73231: can not query data which I have just inserted when partitioning by list

Bug #73231	can not query data which I have just inserted when partitioning by list
Submitted:	8 Jul 2014 12:31	Modified:	20 Mar 2015 15:40
Reporter:	Elmar Stellnberger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.19-log	OS:	Linux (x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	partitioning by list

Description:
When I create a partition by list MySQL fails to find the right partition when querying; see: how to repeat. f.i. If there is a partition with (4,20,35,52) then I would have to query 'where status between 20 and 21' instead of querying 'where status = 20' or 'where status in (20)';

How to repeat:

DROP TABLE IF EXISTS Gruppe;
CREATE TABLE Gruppe (
	gid		 INT unsigned AUTO_INCREMENT,
	status		 TINYINT NOT NULL,
	PRIMARY KEY (gid,status)
) engine=InnoDB default charset=utf8
PARTITION BY LIST COLUMNS (status) (
  PARTITION Deactivated VALUES IN ( -1,-2,-3,-4,-17,-18,-19,-20,-33,-34,-35,-36,-49,-50,-51,-52 ),
  PARTITION Accepted VALUES IN ( 1,2,3,17,18,19 ),
  PARTITION YetAvailable VALUES IN ( 33,34,35,49,50,51 ),
  PARTITION Modifizierer VALUES IN ( 4,20,36,52 ) -- Modifizierer sind ganz normale Aufzählungen
);						
show warnings;

INSERT INTO Gruppe(status) VALUES (20);

SELECT 'status=20';

SELECT * FROM Gruppe WHERE status=20;

EXPLAIN PARTITIONS SELECT * FROM Gruppe WHERE status=20;

SELECT 'all';

SELECT * FROM Gruppe;

> SOURCE bug_partitioning.sql;
Query OK, 0 rows affected (1,19 sec)

Query OK, 0 rows affected (2,67 sec)

Empty set (0,00 sec)

Query OK, 1 row affected (0,18 sec)

+-----------+
| status=20 |
+-----------+
| status=20 |
+-----------+
1 row in set (0,00 sec)

Empty set (0,00 sec)

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Gruppe | NULL       | index | NULL          | PRIMARY | 5       | NULL |    0 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0,00 sec)

+-----+
| all |
+-----+
| all |
+-----+
1 row in set (0,01 sec)

+-----+--------+
| gid | status |
+-----+--------+
|   1 |     20 |
+-----+--------+
1 row in set (0,00 sec)

Hello Elmar,

Thank you for the bug report and test case.
I'm not seeing this issue on latest build(not yet GA) and most likely fixed as part of Bug #71095. Please see change log in Bug #71095

//

// 5.6.19

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS Gruppe;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE Gruppe (
    -> gid INT unsigned AUTO_INCREMENT,
    -> status
Display all 743 possibilities? (y or n)
    -> status  TINYINT NOT NULL,
    -> PRIMARY KEY (gid,status)
    -> ) engine=InnoDB default charset=utf8
    -> PARTITION BY LIST COLUMNS (status) (
    ->   PARTITION Deactivated VALUES IN ( -1,-2,-3,-4,-17,-18,-19,-20,-33,-34,-35,-36,-49,-50,-51,-52 ),
    ->   PARTITION Accepted VALUES IN ( 1,2,3,17,18,19 ),
    ->   PARTITION YetAvailable VALUES IN ( 33,34,35,49,50,51 ),
    ->   PARTITION Modifizierer VALUES IN ( 4,20,36,52 ) -- Modifizierer sind ganz normale Aufzählungen
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO Gruppe(status) VALUES (20);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT 'status=20';
+-----------+
| status=20 |
+-----------+
| status=20 |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM Gruppe WHERE status=20;
Empty set (0.00 sec)

mysql>
mysql> EXPLAIN PARTITIONS SELECT * FROM Gruppe WHERE status=20;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Gruppe | NULL       | index | NULL          | PRIMARY | 5       | NULL |    0 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'all';
+-----+
| all |
+-----+
| all |
+-----+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM Gruppe;
+-----+--------+
| gid | status |
+-----+--------+
|   1 |     20 |
+-----+--------+
1 row in set (0.00 sec)

// 5.6.20

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS Gruppe;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE Gruppe (
    -> gid
Display all 787 possibilities? (y or n)
    -> gid  INT unsigned AUTO_INCREMENT,
    -> status
Display all 787 possibilities? (y or n)
    -> status  TINYINT NOT NULL,
    -> PRIMARY KEY (gid,status)
    -> ) engine=InnoDB default charset=utf8
    -> PARTITION BY LIST COLUMNS (status) (
    ->   PARTITION Deactivated VALUES IN ( -1,-2,-3,-4,-17,-18,-19,-20,-33,-34,-35,-36,-49,-50,-51,-52 ),
    ->   PARTITION Accepted VALUES IN ( 1,2,3,17,18,19 ),
    ->   PARTITION YetAvailable VALUES IN ( 33,34,35,49,50,51 ),
    ->   PARTITION Modifizierer VALUES IN ( 4,20,36,52 ) -- Modifizierer sind ganz normale Aufzählungen
    -> );
Query OK, 0 rows affected (1.09 sec)

mysql> INSERT INTO Gruppe(status) VALUES (20);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT 'status=20';
+-----------+
| status=20 |
+-----------+
| status=20 |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM Gruppe WHERE status=20;
+-----+--------+
| gid | status |
+-----+--------+
|   1 |     20 |
+-----+--------+
1 row in set (0.00 sec)

mysql>
mysql> EXPLAIN PARTITIONS SELECT * FROM Gruppe WHERE status=20;
+----+-------------+--------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | partitions   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Gruppe | Modifizierer | index | NULL          | PRIMARY | 5       | NULL |    2 | Using where; Using index |
+----+-------------+--------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'all';
+-----+
| all |
+-----+
| all |
+-----+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM Gruppe;
+-----+--------+
| gid | status |
+-----+--------+
|   1 |     20 |
+-----+--------+
1 row in set (0.00 sec)

Thanks,
Umesh

Hmm, I get the same results with MySQL-5.7.4-m14-log (latest available version for upgrade).

Very strange; the bug isn`t there on v5.5.31-1~dotdeb.0, (server-installation) while upgrading the MySQL version of my development machine has not helped.

As per the change log in http://bugs.mysql.com/bug.php?id=71095
fixed in 5.7.5, which means 5.7.4 is affected too..

[16 May 13:51] Jonathan 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.

This was certainly not an error of MySQL but a hardware fault because everything worked fine after exchanging the main board; read more at: https://bugzilla.kernel.org/show_bug.cgi?id=95141.

ideally this bug should stay world readable.