| Bug #41823 | Order By fails to sort aliased fields containing spaces | ||
|---|---|---|---|
| Submitted: | 2 Jan 2009 2:13 | Modified: | 2 Jan 2009 21:15 |
| Reporter: | Steve Childs | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.45 | OS: | Linux (Centos) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | order space field name | ||
[2 Jan 2009 7:58]
Valeriy Kravchuk
This is not a bug. You should use backticks or double quotes for identifiers, including aliases, not single quotes. Look:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table test2(name char(100), value int);
Query OK, 0 rows affected (0.38 sec)
mysql> INSERT INTO `test2` (`name`, `value`) VALUES
-> ('field 1', 1),
-> ('field 2', 123),
-> ('field 3', 342),
-> ('field 4', 775),
-> ('field 5', 233),
-> ('field 6', 75),
-> ('field 7', 112);
Query OK, 7 rows affected (0.09 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT name as 'Board Name', value as 'Number of Posts' FROM test2 GROUP
BY name ORDER BY
-> 'Number of Posts' DESC;
+------------+-----------------+
| Board Name | Number of Posts |
+------------+-----------------+
| field 1 | 1 |
| field 2 | 123 |
| field 3 | 342 |
| field 4 | 775 |
| field 5 | 233 |
| field 6 | 75 |
| field 7 | 112 |
+------------+-----------------+
7 rows in set (0.03 sec)
mysql> SELECT name as 'Board Name', value as `Number of Posts` FROM test2 GROUP
BY name ORDER BY `Number of Posts` DESC;
+------------+-----------------+
| Board Name | Number of Posts |
+------------+-----------------+
| field 4 | 775 |
| field 3 | 342 |
| field 5 | 233 |
| field 2 | 123 |
| field 7 | 112 |
| field 6 | 75 |
| field 1 | 1 |
+------------+-----------------+
7 rows in set (0.00 sec)
[2 Jan 2009 13:58]
Steve Childs
When did that change then? As I said, it worked fine in previous versions of mySQL. Never used back ticks before, but then I never had to. I'll bear that in mind now.
[2 Jan 2009 14:21]
Valeriy Kravchuk
The oldest version I have at the moment is 4.1.22, and it does NOT work as you initially expected there:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3306 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 4.1.22-community-nt-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table test2(name char(100), value int);
Query OK, 0 rows affected (0.27 sec)
mysql> INSERT INTO `test2` (`name`, `value`) VALUES
-> ('field 1', 1),
-> ('field 2', 123),
-> ('field 3', 342),
-> ('field 4', 775),
-> ('field 5', 233),
-> ('field 6', 75),
-> ('field 7', 112);
Query OK, 7 rows affected (0.11 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT name as 'Board Name', value as 'Number of Posts' FROM test2 GROUP
BY name ORDER BY
-> 'Number of Posts' DESC;
+------------+-----------------+
| Board Name | Number of Posts |
+------------+-----------------+
| field 1 | 1 |
| field 2 | 123 |
| field 3 | 342 |
| field 4 | 775 |
| field 5 | 233 |
| field 6 | 75 |
| field 7 | 112 |
+------------+-----------------+
7 rows in set (0.13 sec)
If it worked before than it was just a result of a bug that was fixed later. I think it was http://bugs.mysql.com/bug.php?id=14019.
[2 Jan 2009 21:15]
Steve Childs
Ah, must have been fixed then shortly after the 4.1.11 version I was using prior to the upgrade. Oh well, thanks anyway, at least I know what's wrong! :)
Description: If you use an aliased field name in a query and then try and sort on it, MySQL fails to actually do the sort! It returns unsorted information. It did work as of MySQL 4.1.11 (yeah, quite a version jump, sorry!) The actual query this was discovered on retrieves the number of posts made on a vBulletin board for a given date range, its included for reference below SELECT forum.title as "Board Name", Count(*) as "Number of Posts" FROM post LEFT JOIN thread on (post.threadid = thread.threadid) LEFT JOIN forum on (thread.forumid = forum.forumid) WHERE (post.dateline >= UNIX_TIMESTAMP("2008-11-01") and post.dateline <= UNIX_TIMESTAMP("2008-11-30 23:59:59")) GROUP BY thread.forumid ORDER BY "Number of Posts" DESC As you can see, in this query, the ORDER BY field is based upon a Count(*) result, but it fails the same as a straight forward fieldname alias as demonstrated below. it doesn't even appear to be treating the column values as strings as it doesn't even do an alpha sort on the field values, it simply doesn't sort them at all. How to repeat: INSERT INTO `test2` (`name`, `value`) VALUES ('field 1', 1), ('field 2', 123), ('field 3', 342), ('field 4', 775), ('field 5', 233), ('field 6', 75), ('field 7', 112); SELECT name as 'Board Name', value as 'Number of Posts' FROM test2 GROUP BY name ORDER BY 'Number of Posts' DESC Incorrectly returns rows in natural order. SELECT name as 'Board Name', value as 'Number of Posts' FROM test2 GROUP BY name ORDER BY value DESC Returns rows as expected.