Bug #30109 Ambiguous ORDER BY behavior change
Submitted: 27 Jul 2007 23:29 Modified: 30 Jul 2007 14:37
Reporter: Adam Dixon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:4.1.20,4.1.23 - 5.0 OS:Linux
Assigned to: Evgeny Potemkin CPU Architecture:Any

[27 Jul 2007 23:29] Adam Dixon 
Description:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c1` int(11) NOT NULL auto_increment,
  `c2` varchar(16) NOT NULL default '',
  `c3` int(11) NOT NULL default '0',
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (43,'0',0),(57,'0',0),(97,'0',0),(119,'0',0),(124,'0',0),(125,'0',0),(144,'0',0),(159,'0',1),(175,'0',0),(188,'0',3),(226,'0',0),(280,'0',0),(359,'0',0),(366,'0',0),(395,'617868',60408),(462,'14',2),(463,'0',0),(464,'0',82),(465,'0',0),(466,'0',0),(467,'0',5);

SELECT c1, c2 AS 'c 2', c3 as 'c 3' from t1 GROUP BY c1 ORDER BY 'c 2' DESC, c3 ASC;

Slightly possible that these are related patches.
(Bug#24653)(Bug#24562)

Working statement in 4.1.20;

mysql> SELECT c1, c2 AS 'c 2', c3 from t1 GROUP BY c1 ORDER BY 'c 2' DESC, c3 ASC;
+-----+--------+-------+
| c1  | c 2    | c3    |
+-----+--------+-------+
| 395 | 617868 | 60408 | 
| 462 | 14     |     2 | 
| 124 | 0      |     0 | 
| 359 | 0      |     0 | 
| 125 | 0      |     0 | 
| 366 | 0      |     0 | 
| 144 | 0      |     0 | 
|  43 | 0      |     0 | 
| 175 | 0      |     0 | 
| 463 | 0      |     0 | 
|  57 | 0      |     0 | 
|  97 | 0      |     0 | 
| 226 | 0      |     0 | 
| 465 | 0      |     0 | 
| 119 | 0      |     0 | 
| 280 | 0      |     0 | 
| 466 | 0      |     0 | 
| 159 | 0      |     1 | 
| 188 | 0      |     3 | 
| 467 | 0      |     5 | 
| 464 | 0      |    82 | 
+-----+--------+-------+
21 rows in set (0.00 sec)

mysql> select VERSION();
+----------------+
| VERSION()      |
+----------------+
| 4.1.20-max-log | 
+----------------+
1 row in set (0.00 sec)

No longer working in 4.1.23 (same behavior in latest 5.0)

mysql> select VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 4.1.23-pro-gpl-log | 
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT c1, c2 AS 'c 2', c3 from t1 GROUP BY c1 ORDER BY 'c 2' DESC, c3 ASC;
+-----+--------+-------+
| c1  | c 2    | c3    |
+-----+--------+-------+
|  43 | 0      |     0 | 
| 466 | 0      |     0 | 
| 465 | 0      |     0 | 
| 463 | 0      |     0 | 
| 366 | 0      |     0 | 
| 359 | 0      |     0 | 
| 280 | 0      |     0 | 
| 226 | 0      |     0 | 
| 175 | 0      |     0 | 
| 144 | 0      |     0 | 
| 125 | 0      |     0 | 
| 124 | 0      |     0 | 
|  57 | 0      |     0 | 
|  97 | 0      |     0 | 
| 119 | 0      |     0 | 
| 159 | 0      |     1 | 
| 462 | 14     |     2 | 
| 188 | 0      |     3 | 
| 467 | 0      |     5 | 
| 464 | 0      |    82 | 
| 395 | 617868 | 60408 | 
+-----+--------+-------+
21 rows in set (0.00 sec)

=====
Similar sort of handling;

mysql> select i, b as 'test' from a order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'
mysql> select i, b as 'test' from a order by '4';
+------+-------+
| i    | test  |
+------+-------+
|    1 | test  |
|    2 | zed   |
|    3 | alpha |
+------+-------+
3 rows in set (0.01 sec)

How to repeat:
As above queries.

Possible undocumented behavior change, as 5.0 presents the same 4.1.23 results.
[30 Jul 2007 14:37] Evgeny Potemkin 
According to http://dev.mysql.com/doc/refman/5.0/en/identifiers.html :

"The identifier quote character is the backtick (`)"

Thus the 'c 2' in the ORDER BY clause is treated as a string constant and thus doesn't affect the order of the result set. If it is changed to `c 2` the order of rows is correct.

mysql> select i, b as 'test' from a order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'

"ORDER BY 4" means "sort by 4th column" but there is only
two of them. So the message is correct.

"order by '4'"

'4' is treated as a string constant.