MySQL Bugs: #20344: Order BY requires column presence in SELECT

Bug #20344	Order BY requires column presence in SELECT
Submitted:	8 Jun 2006 15:46	Modified:	29 Oct 2019 21:03
Reporter:	bo blanton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.21-max	OS:	MacOS (OS X)
Assigned to:		CPU Architecture:	Any

Description:
This query FAILS to properly ORDER the return result

SELECT community_image.*
FROM community_rate 
LEFT JOIN community_image ON (community_image.id = community_rate.image_id)  WHERE 1 GROUP BY community_rate.image_id  ORDER BY ( sum(community_rate.rating) / count(community_rate.image_id)) DESC LIMIT 0, 2

This one also fails

SELECT community_image.*, sum(community_rate.rating) / count(community_rate.image_id) as rt
FROM community_rate 
 LEFT JOIN community_image ON (community_image.id = community_rate.image_id)  WHERE 1  GROUP BY community_rate.image_id  ORDER BY sum(community_rate.rating) / count(community_rate.image_id) DESC LIMIT 0, 2

But this one sorts properly

SELECT community_image.*, sum(community_rate.rating) / count(community_rate.image_id) as rt
FROM community_rate 
LEFT JOIN community_image ON (community_image.id = community_rate.image_id)  WHERE 1  GROUP BY community_rate.image_id  ORDER BY rt DESC LIMIT 0, 2

How to repeat:
well,

simply create said tables 

CREATE TABLE community_rate (
  id int(11) NOT NULL auto_increment,
  image_id int(11) NOT NULL,
  rating int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE community_image (
  id int(11) NOT NULL auto_increment,
  title int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

populate with some dummy data, and query away.

Suggested fix:
not sure really, the 'work around' is stated in the description, but it seems that the ORDER BY 'column' should automatically be 'present' as a hidden column in the select

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read carefully how ORDER BY works: http://dev.mysql.com/doc/refman/5.0/en/select.html

If not a bug, 

then a feature request... (as this little 'fact' causes many DB-to-Object mappers to fail without some pre data manipulation)

Posted by developer:
 
Ordering works in 5.6 and later.