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

SELECT DISTINCT ... ORDER BY ... LIMIT not sorting results from partitioned table

    XMLWordPrintable

    Details

      Description

      Let's create two tables:

      CREATE TABLE partition_test (
      	id int not null, 
      	status int not null, 
      	descr varchar(255), 
      	PRIMARY KEY (id, status)
      ) ENGINE=innodb
      PARTITION BY LIST(status) (
      	partition s1 values in (0,1,2), 
      	partition s2 values in (3)
      );
       
      CREATE TABLE non_partition_test (
      	id int not null, 
      	status int not null, 
      	descr varchar(255), 
      	PRIMARY KEY (id, status)
      ) ENGINE=innodb;
      

      And then insert the same data into each:

      INSERT INTO partition_test VALUES (1,0,'1/0'), (3,0,'3/0'), (5,0,'5/0'), (2,3,'2/3'), (4,3,'4/3'), (6,3,'6/3');
      INSERT INTO non_partition_test VALUES (1,0,'1/0'), (3,0,'3/0'), (5,0,'5/0'), (2,3,'2/3'), (4,3,'4/3'), (6,3,'6/3');
      

      And then let's compare the results of the following two queries:

      MariaDB [db1]> SELECT DISTINCT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
      +----+-------+
      | id | descr |
      +----+-------+
      |  1 | 1/0   |
      |  3 | 3/0   |
      |  5 | 5/0   |
      |  2 | 2/3   |
      +----+-------+
      4 rows in set (0.00 sec)
       
      MariaDB [db1]> SELECT DISTINCT id, descr FROM non_partition_test ORDER BY id LIMIT 0,4;
      +----+-------+
      | id | descr |
      +----+-------+
      |  1 | 1/0   |
      |  2 | 2/3   |
      |  3 | 3/0   |
      |  4 | 4/3   |
      +----+-------+
      4 rows in set (0.00 sec)
      

      As you can see from the above results, the results from the partitioned table are not being sorted.

      If we remove the DISTINCT keyword, it works:

      MariaDB [db1]> SELECT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
      +----+-------+
      | id | descr |
      +----+-------+
      |  1 | 1/0   |
      |  2 | 2/3   |
      |  3 | 3/0   |
      |  4 | 4/3   |
      +----+-------+
      4 rows in set (0.00 sec)
      

      If we remove the LIMIT, it also works:

      MariaDB [db1]> SELECT DISTINCT id, descr FROM partition_test ORDER BY id;
      +----+-------+
      | id | descr |
      +----+-------+
      |  1 | 1/0   |
      |  2 | 2/3   |
      |  3 | 3/0   |
      |  4 | 4/3   |
      |  5 | 5/0   |
      |  6 | 6/3   |
      +----+-------+
      6 rows in set (0.00 sec)
      

      Query plan:

      MariaDB [db1]> EXPLAIN EXTENDED SELECT DISTINCT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
      +------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
      | id   | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
      +------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
      |    1 | SIMPLE      | partition_test | index | NULL          | PRIMARY | 8       | NULL |    4 |   100.00 | Using temporary |
      +------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [db1]> SHOW WARNINGS;
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                  |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select distinct `db1`.`partition_test`.`id` AS `id`,`db1`.`partition_test`.`descr` AS `descr` from `db1`.`partition_test` order by `db1`.`partition_test`.`id` limit 0,4 |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration