Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11084

Select statement with partition selection against MyISAM table opens all partitions

    Details

    • 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

            Activity

              People

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: