| 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: | |
| 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: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)
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...