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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.18, 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.3.5
Component/s: Partitioning, Storage Engine - MyISAM
Labels:

Sprint:
10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29, 10.1.30, 10.1.31

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

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

Attachments

Issue Links

causes

MDEV-18244 Server crashes in ha_innobase::update_thd / ... / ha_partition::update_next_auto_inc_val

Closed

relates to

MDEV-20250 Implement early partition pruning before statement start

In Progress

MDEV-20376 Assertion in ha_maria::close() upon complex SELECT from specific data

Closed

links to

MySQL Bug #83434 - Select statement with partition selection against MyISAM table opens all partiti

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) created issue - 2016-10-19 00:18

Geoff Montee (Inactive) made changes - 2016-10-19 00:35

Field	Original Value	New Value
Description	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: {noformat} 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); {noformat} And then we execute the following to ensure that the table's data files are closed: {noformat} FLUSH TABLES; {noformat} And then we can confirm that the table's data files are not open: {noformat} $ sudo lsof -p `pidof mysqld` \| grep employees {noformat} And then let's say that we execute the following query: {noformat} SELECT * FROM employees PARTITION (p1); {noformat} Most users would expect that the server would only open the data files for partition p1. In reality, it opens all of them: {noformat} $ 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 {noformat}	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: {noformat} 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); {noformat} And then we execute the following to ensure that the table's data files are closed: {noformat} FLUSH TABLES; {noformat} And then we can confirm that the table's data files are not open: {noformat} $ sudo lsof -p `pidof mysqld` \| grep employees {noformat} And then let's say that we execute the following query: {noformat} SELECT * FROM employees PARTITION (p1); {noformat} Most users would expect that the server would only open the data files for partition p1. In reality, it opens all of them: {noformat} $ 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 {noformat}

Geoff Montee (Inactive) made changes - 2016-10-19 00:36

Remote Link

This issue links to "MySQL Bug #83434 - Select statement with partition selection against MyISAM table opens all partiti (Web Link)" [ 27700 ]

Geoff Montee (Inactive) made changes - 2016-10-19 00:37

Labels

myisam partitioning

myisam partitioning upstream

Elena Stepanova made changes - 2016-10-19 12:07

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2016-10-19 12:09

Reproducible on MySQL 5.6, 5.7.

Elena Stepanova added a comment - 2016-10-19 12:09 Reproducible on MySQL 5.6, 5.7.

Elena Stepanova made changes - 2016-10-19 12:09

Fix Version/s		10.2 [ 14601 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]

Alexey Botchkov made changes - 2016-10-25 13:37

Assignee

Alexey Botchkov [ holyfoot ]

Alexey Botchkov made changes - 2016-10-26 08:42

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Rasmus Johansson (Inactive) made changes - 2016-10-26 20:06

Sprint

10.1.19 [ 109 ]

Rasmus Johansson (Inactive) made changes - 2016-10-26 20:06

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2016-12-02 08:00

Sprint

10.1.19 [ 109 ]

10.1.19, 10.1.20 [ 109, 119 ]

Rasmus Johansson (Inactive) made changes - 2016-12-02 08:00

Rank

Ranked higher

Sergei Golubchik made changes - 2016-12-09 10:31

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2016-12-09 10:31

Affects Version/s

10.2 [ 14601 ]

Rasmus Johansson (Inactive) made changes - 2017-01-11 07:55

Sprint

10.1.19, 10.1.20 [ 109, 119 ]

10.1.19, 10.1.20, 10.1.21 [ 109, 119, 130 ]

Rasmus Johansson (Inactive) made changes - 2017-01-18 08:57

Sprint

10.1.19, 10.1.20, 10.1.21 [ 109, 119, 130 ]

10.1.19, 10.1.20, 10.2.4-1 [ 109, 119, 132 ]

Rasmus Johansson (Inactive) made changes - 2017-01-18 08:57

Rank

Ranked lower

Rasmus Johansson (Inactive) made changes - 2017-01-26 10:37

Sprint

10.1.19, 10.1.20, 10.2.4-1 [ 109, 119, 132 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2 [ 109, 119, 132, 134 ]

Sergei Golubchik made changes - 2017-02-21 14:36

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2 [ 109, 119, 132, 134 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.0.30 [ 109, 119, 132, 134, 140 ]

Rasmus Johansson (Inactive) made changes - 2017-03-02 11:22

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.0.30 [ 109, 119, 132, 134, 140 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22 [ 109, 119, 132, 134, 143 ]

Rasmus Johansson (Inactive) made changes - 2017-04-20 09:10

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22 [ 109, 119, 132, 134, 143 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23 [ 109, 119, 132, 134, 143, 154 ]

Rasmus Johansson (Inactive) made changes - 2017-04-20 09:10

Rank

Ranked lower

Rasmus Johansson (Inactive) made changes - 2017-05-18 12:50

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23 [ 109, 119, 132, 134, 143, 154 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.1.24 [ 109, 119, 132, 134, 143, 154, 161 ]

Rasmus Johansson (Inactive) made changes - 2017-05-24 12:00

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.1.24 [ 109, 119, 132, 134, 143, 154, 161 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1 [ 109, 119, 132, 134, 143, 154, 164 ]

Rasmus Johansson (Inactive) made changes - 2017-05-24 12:00

Rank

Ranked lower

Alexey Botchkov added a comment - 2017-06-01 14:15

Patch proposal:
http://lists.askmonty.org/pipermail/commits/2017-June/011221.html

Alexey Botchkov added a comment - 2017-06-01 14:15 Patch proposal: http://lists.askmonty.org/pipermail/commits/2017-June/011221.html

Alexey Botchkov made changes - 2017-06-01 14:15

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexey Botchkov added a comment - 2017-06-06 08:21 - edited

Updated patch.
http://lists.askmonty.org/pipermail/commits/2017-June/011237.html

Alexey Botchkov added a comment - 2017-06-06 08:21 - edited Updated patch. http://lists.askmonty.org/pipermail/commits/2017-June/011237.html

Sergei Golubchik made changes - 2017-10-31 19:10

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1 [ 109, 119, 132, 134, 143, 154, 164 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29 [ 109, 119, 132, 134, 143, 154, 164, 202 ]

Sergei Golubchik made changes - 2017-12-12 11:32

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29 [ 109, 119, 132, 134, 143, 154, 164, 202 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29, 10.1.30 [ 109, 119, 132, 134, 143, 154, 164, 202, 215 ]

Sergei Golubchik made changes - 2017-12-12 11:32

Rank

Ranked higher

Sergei Golubchik made changes - 2017-12-15 20:39

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexey Botchkov added a comment - 2017-12-18 16:03

http://lists.askmonty.org/pipermail/commits/2017-December/011736.html

Alexey Botchkov added a comment - 2017-12-18 16:03 http://lists.askmonty.org/pipermail/commits/2017-December/011736.html

Alexey Botchkov made changes - 2017-12-18 19:14

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2017-12-19 20:14

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.1 [ 16100 ]

Sergei Golubchik made changes - 2017-12-20 00:14

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexey Botchkov made changes - 2018-01-24 13:27

Sprint

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29, 10.1.30 [ 109, 119, 132, 134, 143, 154, 164, 202, 215 ]

10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29, 10.1.30, 10.1.31 [ 109, 119, 132, 134, 143, 154, 164, 202, 215, 225 ]

Alexey Botchkov made changes - 2018-01-26 11:31

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexey Botchkov added a comment - 2018-01-29 07:32

The final patch.
http://lists.askmonty.org/pipermail/commits/2018-January/011911.html

Alexey Botchkov added a comment - 2018-01-29 07:32 The final patch. http://lists.askmonty.org/pipermail/commits/2018-January/011911.html

Alexey Botchkov made changes - 2018-01-29 07:32

issue.field.resolutiondate

2018-01-29 07:32:41.0

2018-01-29 07:32:41.475

Alexey Botchkov made changes - 2018-01-29 07:32

Fix Version/s		10.3.5 [ 22905 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2019-01-18 04:10

Link

This issue causes ~~MDEV-18244~~ [ ~~MDEV-18244~~ ]

Sergei Golubchik made changes - 2019-08-05 08:37

Link

This issue relates to MDEV-20250 [ MDEV-20250 ]

Aleksey Midenkov made changes - 2019-08-21 09:46

Link

This issue relates to ~~MDEV-20376~~ [ ~~MDEV-20376~~ ]

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 78000 ]

MariaDB v4 [ 151090 ]

Jira Automation (IT) made changes - 2024-07-04 08:46

Zendesk Related Tickets

152974

People

Assignee:: Alexey Botchkov

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2016-10-19 00:18

Updated:: 2024-07-08 01:11

Resolved:: 2018-01-29 07:32

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration