Bug #82572 Sorting a list based on some value under associated table containing text field
Submitted: 13 Aug 2016 4:52 Modified: 13 Aug 2016 7:43
Reporter: Sudhir Shrestha Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql Ver 14.14 Distrib 5.7.13, for Lin OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: sorting, text

[13 Aug 2016 4:52] Sudhir Shrestha
Description:
SELECT items.id, items.description, avg(ratings.value) as average
 FROM `items` 
INNER JOIN `ratings` ON `ratings`.`item_id` = `items`.`id` 
WHERE `items`.`type` IN ('Book') 
GROUP BY ratings.item_id 
ORDER BY average desc;

When using "order by" based on some child table which has a text field, the output collection is not sorted as expected.

How to repeat:
I had listed this out in stackoverflow question,

http://stackoverflow.com/questions/38915533/sorting-a-list-based-on-the-average-value-of-f...
[13 Aug 2016 4:55] Sudhir Shrestha
Edit bug Synopsis
[13 Aug 2016 7:32] MySQL Verification Team
Just for discussion, I think this testcase shows the reported scenario?
--

drop table if exists s,t;
create table s(a int not null auto_increment primary key,b text character set latin1)engine=innodb;
create table t(c int not null auto_increment primary key,d int)engine=innodb;
insert into s(b) values('a'),('b'),('c');
insert into s(b) select 'a' from s,s a,s b,s c,s d,s e,s f,s g;

insert into t(d) values(1),(2),(3);
insert into t(d) select floor(rand()*1000) from t,t a,t b,t c,t d,t e,t f,t g;

set sql_mode='only_full_group_by';
select s.a,s.b,avg(t.d) as average from s
inner join t on s.a=t.c
where s.a%1000 = 0
group by t.c
order by average desc;

--

mysql> select s.a,s.b,avg(t.d) as average from s
    -> inner join t on s.a=t.c
    -> where s.a%1000 = 0
    -> group by t.c
    -> order by average desc;
+------+------+----------+
| a    | b    | average  |
+------+------+----------+
| 6000 | a    | 163.0000 |
| 5000 | a    | 949.0000 |
| 4000 | a    | 936.0000 |
| 3000 | a    | 319.0000 |
| 2000 | a    | 882.0000 |
| 1000 | a    | 694.0000 |
+------+------+----------+
6 rows in set (0.18 sec)
[13 Aug 2016 7:33] MySQL Verification Team
On same testcase, 5.6.32 says "ERROR 1055 (42000): 'test.s.a' isn't in GROUP BY"
[13 Aug 2016 7:36] MySQL Verification Team
On 5.7,  the workaround to get a correct result is to run:

set global internal_tmp_disk_storage_engine=MyISAM;
[13 Aug 2016 7:39] MySQL Verification Team
Fairly sure this is a duplicate of the bug:

http://bugs.mysql.com/bug.php?id=79366 
(ORDER BY does not work correctly with grouped AVG() values extracted from JSON)