Weird Group/sort behavior in mysql 5.7

Question

I am not DB guy and I could not get why this simple case work perfectly on 5.6 but fail on 5.7 the weird way.

Use case: get the list of TOP-rated comic along with some info like title, author...

As long as I select only non-text colunm , then it sort correctly

            SELECT c.id, c.title IF(AVG(r.score) IS NOT NULL, AVG(r.score), 0) as `rateScore`,  COUNT(r.`entityId`) as `rateCount`  
        FROM `manga_comic` as c
        LEFT JOIN `base_rate` as r
        ON( r.`entityType` = 'comic' AND c.id = r.`entityId` )


        GROUP BY c.`id`
        ORDER BY `rateScore` DESC, rateCount DESC

Adding a text column like "c.description" will ignore rateScore while value rateCount.

Again, only 5.7 for me (or maybe some configuration which mess things up). I hope not to recode alot of screen just because of upgrading to mysql 5.7

Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) — e4c5, Aug 16 '16 at 12:43
Is the text column being added included in both the select and group by? — xQbert, Aug 16 '16 at 12:45
@e4c5 : that's some "seriously" quick reply, it looks promising, thanks — Phung D. An, Aug 16 '16 at 12:46
I don't see how adding `c.description` (to the select clause I assume) could have any effect on `group by` or `order by`. It doesn't even have to be added to `group by`, because it is functionally dependent on `c.id`. Can you please elaborate what exactly is different? (I don't understad your explanation "will ignore rateScore while value rateCount".) Can you post the altered query that is causing the problems? — Thorsten Kettner, Aug 16 '16 at 12:54
@Strawberry: Are you referring to my comment? `c.description` should be functionally dependent on `c.id`. So it suffices to have `c.id` in `group by` in current standard SQL. As long as `ONLY_FULL_GROUP_BY` is not set, MySQL should be able to run the statement with `c.description` added to the select clause without problems. — Thorsten Kettner, Aug 16 '16 at 13:31

score 0 · Answer 1 · edited May 23 '17 at 11:51

I got it works. Thanks to the hint from the answer of Is there ANY_VALUE capability for mysql 5.6?

        SELECT DISTINCT c.id,
    c.author,
    c.title,
    ....... ,

    r.avgScore as `rateScore`,  r.`rateCount` ,

    f.id as mediaId , f.type as mediaType, f.data as cover

                FROM `manga_comic` as c
                LEFT JOIN (
                Select entityId,   AVG(r0.score) as `avgScore`,  COUNT(r0.`entityId`) as `rateCount` 
                FROM `base_rate` as r0

                Where  r0.`entityType` = 'comic'
                    GROUP BY r0.`entityType`  , r0.`entityId`
                ) r
                ON(  c.id = r.`entityId` )



                LEFT JOIN entity e on ( `c`.`id` = `e`.`entityId` and e.entityType='comic' and plugin='manga' )
                LEFT JOIN media_file f on ( `e`.`id` = `f`.`entityId` and f.type='cover')
                WHERE 1 


                ORDER BY `rateScore` DESC , rateCount DESC
                LIMIT 0, 20

Thank you e4c5 for the link and others for your time and support.

Conclusion: so far I had so many trouble from mysql 5.7 so think twice before upgrading your PROD to 5.7

Weird Group/sort behavior in mysql 5.7

1 Answers1