Description:
When a user uses partition selection in a SELECT query to only query a particular partition of a MyISAM table, many users would expect the server to only open the data files for that particular partition. In reality, the server opens all data files for the table.
This bug is related to partition selection in SELECT statements, as defined here:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
How to repeat:
Let's say that we create the following table:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id INT NOT NULL,
department_id INT NOT NULL
) ENGINE=MyISAM
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
And then we execute the following to ensure that the table's data files are closed:
FLUSH TABLES;
And then we can confirm that the table's data files are not open:
$ sudo lsof -p `pidof mysqld` | grep employees
And then let's say that we execute the following query:
SELECT * FROM employees PARTITION (p1);
Most users would expect that the server would only open the data files for partition p1. In reality, it opens all of them:
$ sudo lsof -p `pidof mysqld` | grep employees
mysqld 825 mysql 18u REG 202,2 2048 50336245 /var/lib/mysql/db1/employees#P#p0.MYI
mysqld 825 mysql 20u REG 202,2 120 50336246 /var/lib/mysql/db1/employees#P#p0.MYD
mysqld 825 mysql 23u REG 202,2 2048 50336247 /var/lib/mysql/db1/employees#P#p1.MYI
mysqld 825 mysql 24u REG 202,2 140 50336248 /var/lib/mysql/db1/employees#P#p1.MYD
mysqld 825 mysql 25u REG 202,2 2048 50336249 /var/lib/mysql/db1/employees#P#p2.MYI
mysqld 825 mysql 26u REG 202,2 148 50336250 /var/lib/mysql/db1/employees#P#p2.MYD
mysqld 825 mysql 27u REG 202,2 2048 50336251 /var/lib/mysql/db1/employees#P#p3.MYI
mysqld 825 mysql 28u REG 202,2 120 50336252 /var/lib/mysql/db1/employees#P#p3.MYD
Suggested fix:
Only open the data files for the partition being selected when partition selection is in use.
