[MDEV-10449] SELECT DISTINCT ... ORDER BY ... LIMIT not sorting results from partitioned table Created: 2016-07-26  Updated: 2020-08-25  Resolved: 2018-07-18

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Storage Engine - InnoDB
Affects Version/s: 10.1.14, 5.5, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 2
Labels: innodb, partitioning, upstream-fixed

Issue Links:
Duplicate
duplicates MDEV-10450 SELECT DISTINCT...ORDER BY for partit... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2016-07-26 ]

Reproducible on 5.5, 10.0, 10.1, MySQL 5.5.
Not reproducible on MySQL 5.6 and MariaDB 10.2.

In 10.2, the problem disappeared after the revision below; apparently, the fix was a side-effect of the intended change. I'll pass it over to psergey to decide whether the fixing part can be backported to 10.1, or a separate fix is needed. 

commit 2cfc450bf78c2d951729d1a0e8f731c0d987b1d5
 
Author: Igor Babaev <igor@askmonty.org>
 
Date:   Tue Feb 9 12:35:59 2016 -0800
 
 
 
    This is the consolidated patch for mdev-8646:
 
    "Re-factor the code for post-join operations".
 
    
    The patch mainly contains the code ported from mysql-5.6 and
 
    created for two essential architectural changes:
 
    1. WL#5558: Resolve ORDER BY execution method at the optimization stage
 
    2. WL#6071: Inline tmp tables into the nested loops algorithm
 
    
    The first task was implemented for mysql-5.6 by Ole John Aske.
 
    It allows to make all decisions on ORDER BY operation at the optimization
 
    stage.
 
    
    The second task implemented for mysql-5.6 by Evgeny Potemkin adds JOIN_TAB
 
    nodes for post-join operations that require temporary tables. It allows
 
    to execute these operations within the nested loops algorithm that used to
 
    be used before this task only for join queries. Besides these task moves
 
    all planning on the execution of these operations from the execution phase
 
    to the optimization phase.
 
    
    Some other re-factoring changes of mysql-5.6 were pulled in, mainly because
 
    it was easier to pull them in than roll them back. In particular all
 
    changes concerning Ref_ptr_array were incorporated.
 
    
    The port required some changes in the MariaDB code that concerned the
 
    functionality of EXPLAIN and ANALYZE. This was done mainly by Sergey
 
    Petrunia.

Comment by Sergei Petrunia [ 2018-07-18 ]

Backporting the above-mentioned fix is practically impossible - it does a huge re-engineering, and also there were bugfixes made after it. We can't get a big portion of 10.2 into 10.1.

Fixing this particular bug in 10.1 might be hard and has a risk of introducing other errors. (there is a reason why patch 2cfc450bf78c2d951729d1a0e8f731c0d987b1d5 was huge and took a long time to develop).

Considering that there are two stable releases where this bug is not observed - 10.2 and 10.3, I think we can leave this bug without resolution and recommend to upgrade to 10.2 or 10.3

Generated at Thu Feb 08 07:42:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.