[MDEV-10450] SELECT DISTINCT...ORDER BY for partitioned tables is not working properly Created: 2016-07-27 Updated: 2016-07-27 Resolved: 2016-07-27 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Optimizer, Partitioning |
| Affects Version/s: | 10.0.20 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Marcos Albe | Assignee: | Unassigned |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Issue Links: |
|
||||||||
| 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:
With DISTINCT:
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;
|
| Comments |
| Comment by Marcos Albe [ 2016-07-27 ] | ||||||||||||||
|
Using GROUP BY instead of distinct also does the right thing (test) > select id, descr from bugtest group by concat_ws('-',id,descr) order by id asc limit 0,6;
6 rows in set (0.00 sec | ||||||||||||||
| Comment by Elena Stepanova [ 2016-07-27 ] | ||||||||||||||
|
See also | ||||||||||||||
| Comment by Marcos Albe [ 2016-07-27 ] | ||||||||||||||
|
Ooops, seems my customer already submitted and I wasn't able to find it; Thanks for pointing to it, Elena. Feel free to close this one! |