[#MDEV-11084] Select statement with partition selection against MyISAM table opens all partitions

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

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

[MDEV-11084] Select statement with partition selection against MyISAM table opens all partitions Created: 2016-10-19 Updated: 2020-08-25 Resolved: 2018-01-29
Status:	Closed
Project:	MariaDB Server
Component/s:	Partitioning, Storage Engine - MyISAM
Affects Version/s:	10.1.18, 10.0, 10.1
Fix Version/s:	10.3.5

[MDEV-11084] Select statement with partition selection against MyISAM table opens all partitions Created: 2016-10-19 Updated: 2020-08-25 Resolved: 2018-01-29