Mysql returning "Group By" error after upgrade to version 5.7

Question

I had a certain MYSQL query that was working correctly.

This is the consultation:

SELECT *, count(*) as count FROM `post_hashtags`
WHERE `created_at` BETWEEN ? AND ? 
    AND (SELECT count(*) AS `aggregate` 
FROM `post` 
WHERE `post`.`id` = post_hashtags.post_id 
    AND `status` = 0 
    AND `post_privacidade_id` = 1) >= ? 
GROUP BY `hashtag` 
ORDER BY `count` DESC, created_at DESC 
LIMIT 5

But after upgrading Mysql to version 5.7 on my machine, I started getting this error:

Syntax error or access Violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'post_hashtags.id' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Does anyone know what’s going on? I’ve never received this message before.

Have something considered wrong in the query, for the new version of Mysql?

Answer 1

All fields that are in SELECT and are not using functions (such as COUNT, SUM, AVG, MAX, MIN, etc.) must be in the GROUP BY clause.

In your case, as you are using asterisk, you should have all fields of the table in the clause GROUP BY.

Answer 2

I solved the problem by disabling the sql_mode.

I did so:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

According to this reply from SOEN