Bug #7422 "order by" doesn't work
Submitted: 20 Dec 2004 5:02 Modified: 21 Jun 2005 17:12
Reporter: Yuan Paul Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Any (all)
Assigned to: Evgeny Potemkin

[20 Dec 2004 5:02] Yuan Paul
Description:
1.When I have created a view using a table and another view,if I select * and use "order by",the results are ordered;but if I select Id,CreateDate and so on,"order by" doesn't work.I think it is a serious bug. 
2.When I create a view,if one table hasn't "CreateDate"(datetime),I use '' or 0 as CreateDate,in the view the column "CreateDate" becomes String '' or 0,but not 0000-00-00 00:00:00

How to repeat:
1.create two tables and use union to a view,and then create another table ,use the table and the view create a view ,then you can find out that select * .....order by ... is different from that select Id,.... order by ....
[20 Dec 2004 6:07] Heikki Tuuri
Hi!

Please post an EXACT sequence of SQL statements that demonstrates the problem.

Regards,

Heikki
[21 Dec 2004 1:33] Yuan Paul
create database testview;
use testview;

/*create two same structral table*/
CREATE TABLE testlink1 (ParentId char(10) default NULL,ChildId  char(10) default NULL);
CREATE TABLE testlink2 (ParentId char(10) default NULL,ChildId  char(10) default NULL);

/*union them to a view*/
CREATE VIEW testlinkinfoview AS 
select testlink1.ParentId,testlink1.ChildId from testlink1
union
select testlink2.ParentId,testlink2.ChildId from testlink2;

/*create another table*/
CREATE TABLE info(Id char(10) NOT NULL PRIMARY KEY,Name char(10) default NULL,CreateDate datetime default NULL);

/*create a view with the view and the new table*/
CREATE VIEW testview AS
select testlinkinfoview.ParentId,info.* from testlinkinfoview join info on testlinkinfoview.ChildId=info.Id;

/*insert data into all tables*/
insert into testlink1 values('a','1');
insert into testlink1 values('b','2');
insert into testlink2 values('c','3');
insert into testlink2 values('d','4');
insert into testlink2 values('e','5');

insert into info values('1','Aug','2004-1-5');
insert into info values('2','Tana','2003-6-17');
insert into info values('3','Sota','2004-12-17');
insert into info values('4','Bin','2002-3-25');
insert into info values('5','Cilly','2003-11-7');

/********************************************************
query:
mysql> select * from testview;
+----------+----+-------+---------------------+
| ParentId | Id | Name  | CreateDate                 |
+----------+----+-------+---------------------+
| a           | 1     | Aug    | 2004-01-05 00:00:00|
| b           | 2     | Tana  | 2003-06-17 00:00:00|
| c           | 3     | Sota   | 2004-12-17 00:00:00|
| d           | 4     | Bin     | 2002-03-25 00:00:00|
| e           | 5     | Cilly    | 2003-11-07 00:00:00|
+----------+----+-------+---------------------+
5 rows in set (0.02 sec)

mysql> select * from testview order by Name;
+----------+----+-------+---------------------+
| ParentId  | Id   | Name  | CreateDate              |
+----------+----+-------+---------------------+
| a            | 1   | Aug     | 2004-01-05 00:00:00 |
| d            | 4   | Bin      | 2002-03-25 00:00:00 |
| e            | 5   | Cilly     | 2003-11-07 00:00:00 |
| c            | 3   | Sota    | 2004-12-17 00:00:00 |
| b            | 2   | Tana   | 2003-06-17 00:00:00 |
+----------+----+-------+---------------------+
5 rows in set (0.00 sec)

mysql> select Name from testview order by Name;
+-------+
| Name  |
+-------+
| Aug    |
| Tana   |
| Sota   |
| Bin     |
| Cilly    |
+-------+
5 rows in set (0.01 sec)

mysql> select ParentId,Id,Name,CreateDate from testview order by CreateDate
+----------+----+-------+---------------------+
| ParentId  | Id   | Name  | CreateDate              |
+----------+----+-------+---------------------+
| d            | 4   | Bin      | 2002-03-25 00:00:00 |
| a            | 1   | Aug     | 2004-01-05 00:00:00 |
| e            | 5   | Cilly     | 2003-11-07 00:00:00 |
| b            | 2   | Tana   | 2003-06-17 00:00:00 |
| c            | 3   | Sota   | 2004-12-17 00:00:00 |
+----------+----+-------+---------------------+
5 rows in set (0.00 sec)
*************************************************************/
[23 Dec 2004 15:29] Victoria Reznichenko
Verified with 5.0.3-alpha-debug-log
OS: Windows, Linux.
[26 Apr 2005 9:28] Holger S.
In MySQL-Documentation to CREATE VIEW syntax i found:
( http://dev.mysql.com/doc/mysql/en/create-view.html )
-> ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

Does that  mean that this issue is never going to be fixed?

A temporary workaround until it's fixed:

SELECT * FROM 
  (select ParentId,Id,Name,CreateDate from testview) as unsorted
ORDER BY CreateDate
[17 Jun 2005 12:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26109
[17 Jun 2005 20:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26140
[21 Jun 2005 13:31] Evgeny Potemkin
Fixed in 5.0.8, cset 1.1952
[21 Jun 2005 17:12] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.8 Change History; marked Closed.