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: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.2 | OS: | Any (all) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[20 Dec 2004 5:02]
Yuan Paul
[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]
MySQL Verification Team
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.