- Error Code: 1055. 'test.somefield' isn't in GROUP BY
- SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.somefield' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT @@sql_mode;
This will give a list of sql_modes enabled in the server. Example as follows,
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
As you can see from the above result, you have a value called "only_full_group_by". That means if you have a group by clause in your sql statement, only the fields which are listed in a group by clause can be used in the select clause or order by clause, unless you aggregate the fields. i.e, using aggregate functions such as min(), max() etc
NOTE: As of MySQL Server version 5.7.5, the only_full_group_by is enabled in the sql_mode by default.
Temporary Fix:
Temporarily you can fix this issue by disabling this check in the server by globally and/or only in the active session by issuing the following command respectively.
SET GLOBAL sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
SET SESSION sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Permanent Fix:
- Make sure nonagregated field list in select or order by clause are in group by
- Restructure the sql statement
No comments:
Post a Comment