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: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7.13 | OS: | 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
[13 Jun 2016 17:00]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.