Bug #275 ORDER BY being discarded and results are returned un-sorted
Submitted: 11 Apr 2003 9:57 Modified: 11 Apr 2003 9:58
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0 OS:Any (ALL)
Assigned to: Sergey Glukhov

[11 Apr 2003 9:57] Sinisa Milivojevic
Description:
Although ORDER BY is issued on a column in the select list, rows in result set are un-sorted.

How to repeat:
drop table if exists t1;
create table t1  ( pk         int primary key,    parent     int not null,    child      int not null,       index (parent)  ) type = innodb;
insert into t1 values   (1,0,4),  (2,1,3),  (3,2,1),  (4,1,2);
select distinct  parent,child   from t1   order by parent;
drop table t1;

Suggested fix:
fix the optimiser
[11 Apr 2003 9:58] Sinisa Milivojevic
===== sql/sql_select.cc 1.228 vs edited =====
*** /tmp/sql_select.cc-1.228-3602       Thu Mar 13 13:42:44 2003
--- edited/sql/sql_select.cc    Fri Apr 11 19:33:39 2003
***************
*** 555,560 ****
--- 555,562 ----
        /*  Change DISTINCT to GROUP BY */
        select_distinct= 0;
        no_order= !order;
+       if (order && skip_sort_order)
+         join.tmp_table_param.quick_group=0;
        if (all_order_fields_used)
          order=0;
        join.group=1;                           // For end_write_group