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

[13 Jun 2016 16:55] Kamil Gregorczyk
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
[13 Jun 2016 17:00] Miguel Solorzano
Please provide here the complete test case (create table, insert data, query real result, query expected result). Thanks.
[13 Jun 2016 17:16] Kamil Gregorczyk
Sample data + query to reproduce

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

[13 Jun 2016 17:37] Miguel Solorzano
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)
[13 Jun 2016 17:43] Miguel Solorzano
Thank you for the feedback.
[13 Jun 2016 22:56] Knut Anders Hatlen
This seems to be the same problem as reported in bug#79366, so I'm closing this report as a duplicate. Thanks.