Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.20
-
None
Description
When doing SELECT DISTINCT ... ORDER BY on a partitioned table, the ordering is wrong:
MariaDB [master_biz]> create table bugtest (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));
MariaDB [master_biz]> insert into bugtest 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');
Without DISTINCT:
MariaDB [master_biz]> select id, descr from bugtest order by id limit 0,4;
id | descr |
1 | 1/0 |
2 | 2/3 |
3 | 3/0 |
4 | 4/3 |
With DISTINCT:
MariaDB [master_biz]> select distinct id, descr from bugtest order by id limit 0,4;
id | descr |
1 | 1/0 |
3 | 3/0 |
5 | 5/0 |
2 | 2/3 |
The order of id's works only within a partition; When we do DESC sorting then things appear to be fine:
mysql [localhost]
{msandbox}(test) > select distinct id, descr from bugtest order by id desc limit 0,6;
id | descr |
6 | 6/3 |
5 | 5/0 |
4 | 4/3 |
3 | 3/0 |
2 | 2/3 |
1 | 1/0 |
Attachments
Issue Links
- is duplicated by
-
MDEV-10449 SELECT DISTINCT ... ORDER BY ... LIMIT not sorting results from partitioned table
- Closed