MySQL Bugs: #7422: "order by" doesn't work

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	CPU Architecture:	Any

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 ....

Hi!

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

Regards,

Heikki

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)
*************************************************************/

Verified with 5.0.3-alpha-debug-log
OS: Windows, Linux.

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

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

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

Fixed in 5.0.8, cset 1.1952

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.