MySQL Bugs: #81835: Ordering by aggregation column does not work when longtext column is present

Bug #81835	Ordering by aggregation column does not work when longtext column is present
Submitted:	13 Jun 2016 16:55	Modified:	13 Jun 2016 22:56
Reporter:	Kamil Gregorczyk	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.13	OS:	Microsoft Windows (windows 10 and mac el capitan)
Assigned to:		CPU Architecture:	Any
Tags:	aggregate ordering, longtext, order by

Description:
When I want to order my query by aggregation column order by simply does not work, it works only when I'm not printing column of type longtext (in my sample data, item.content). It's really weird becouse it works when I'm ordering by for example title, or pk but when I want to order by aggregated column and longtext column is present it's not working.
I have this problem on windows 10 and mac osx with mysql 5.7.13 with utf8_polish encoding.

How to repeat:
Here's link do sample data http://pastebin.com/50Nj9WKH

and here's query which should output first item with 1.5 rating and then with 3.0 but it's not working, when i change ASC to DESC it gives same answer. This bug is not present in 5.6, i checked it on fiddle and everything works http://sqlfiddle.com/#!9/d3d6061/1

SELECT
  `item`.`id`,
  `item`.`content`,
  `item`.`title`,
  AVG(`review`.`rating`) AS 'testowo'
FROM
  `product`
INNER JOIN
  `item` ON(
    `product`.`item_parent_id` = `item`.`id`
  )
LEFT OUTER JOIN
  `review` ON(`item`.`id` = `review`.`item_id`)
GROUP BY
  `product`.`item_parent_id`
ORDER BY
  `testowo` ASC

Suggested fix:
no idea

Please provide here the complete test case (create table, insert data, query real result, query expected result). Thanks.

Sample data + query to reproduce

Attachment: bug_longtext.sql (application/octet-stream, text), 2.99 KiB.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.14-debug Source distribution PULL: 2016-MAY-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > SELECT
    ->   `item`.`id`,
    ->   `item`.`content`,
    ->   `item`.`title`,
    ->   AVG(`review`.`rating`) AS 'testowo'
    -> FROM
    ->   `product`
    -> INNER JOIN
    ->   `item` ON(
    ->     `product`.`item_parent_id` = `item`.`id`
    ->   )
    -> LEFT OUTER JOIN
    ->   `review` ON(`item`.`id` = `review`.`item_id`)
    -> GROUP BY
    ->   `product`.`item_parent_id`
    -> ORDER BY
    ->   `testowo` ASC
    -> ;
+----+----------------+-------+---------+
| id | content        | title | testowo |
+----+----------------+-------+---------+
|  1 | some content   |   123 |  3.0000 |
|  2 | some content 2 |   456 |  1.5000 |
+----+----------------+-------+---------+
2 rows in set (0.01 sec)

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.32 Source distribution PULL: 2016-MAY-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > SELECT
    ->   `item`.`id`,
    ->   `item`.`content`,
    ->   `item`.`title`,
    ->   AVG(`review`.`rating`) AS 'testowo'
    -> FROM
    ->   `product`
    -> INNER JOIN
    ->   `item` ON(
    ->     `product`.`item_parent_id` = `item`.`id`
    ->   )
    -> LEFT OUTER JOIN
    ->   `review` ON(`item`.`id` = `review`.`item_id`)
    -> GROUP BY
    ->   `product`.`item_parent_id`
    -> ORDER BY
    ->   `testowo` ASC
    -> ;
+----+----------------+-------+---------+
| id | content        | title | testowo |
+----+----------------+-------+---------+
|  2 | some content 2 |   456 |  1.5000 |
|  1 | some content   |   123 |  3.0000 |
+----+----------------+-------+---------+
2 rows in set (0.01 sec)

Thank you for the feedback.

This seems to be the same problem as reported in bug#79366, so I'm closing this report as a duplicate. Thanks.