Mysql returning "Group By" error after upgrade to version 5.7 [duplicate]

Navigation
2

I had a certain MYSQL query that was working correctly.

This is the query: 

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.

Something is considered wrong in the query, for the new version of the Mysql?

    
asked by anonymous 10.11.2016 / 13:40

2 answers

8

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

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

    
10.11.2016 / 13:45
5

I solved the problem by disabling sql_mode .

I did so: 

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

According to this response from SOEN

    
10.11.2016 / 14:21
Fill fields in a multidimensional array with null Javac does not compile .java