Bug #83434 Select statement with partition selection against MyISAM table opens all partiti
Submitted: 19 Oct 2016 0:35 Modified: 19 Oct 2016 7:20
Reporter: Geoff Montee Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.6.30 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2016 0:35] Geoff Montee
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.
[19 Oct 2016 7:20] Umesh Shastry
Hello Geoff Montee,

Thank you for the report.
Imho this expected behavior and is duplicate of your earlier Bug #75753 which was later marked as duplicate of Bug #64498. Let me re-quote which was explained nicely by Mattias Jonsson in Bug #64498 "This is a design limitation, MyISAM have one file handle open for the .MYI file shared by all instances of one table, and one file handle open for each table instance for the .MYD file. And the ha_partition generic partitioning engine always open all partitions when opening a table/handler".. more details in #64498

As part of Bug#64498, documentation has been updated now in http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html

Thanks,
Umesh