MySQL only_full_group_by error

Question

I am not a DBA and I inherited this query and i'm not quite sure why I'm getting a sql_mode=only_full_group_by error. Especially since the error is talking about result.fullURL and i don't see that in the query. Could someone please explain this to me?

Query

SELECT *
FROM (
    SELECT content.*, content.navigationOrder AS sortOrder
    FROM LB_Content AS content
    INNER JOIN LB_Content AS other ON content.contentSectionId = other.contentSectionId
    WHERE other.fullURL = '/index'
    AND content.contentSlug <> 'index'
    GROUP BY content.contentId
    UNION
    SELECT content.*, query.sectionOrder AS sortOrder
    FROM LB_Content AS content, (
        SELECT section.*
        FROM LB_ContentSections AS section
        INNER JOIN LB_Content AS other ON section.parentContentSectionId = other.contentSectionId
        WHERE other.fullURL = '/index'
    ) AS query
    WHERE content.contentSectionId = query.contentSectionId
    AND content.contentSlug = 'index'
) as result,
LB_ContentTypes AS types
WHERE result.showInNavigation = 1
AND result.status = 1
AND result.published = 1
AND result.contentTypeId = types.contentTypeId
AND types.useOption = 1
GROUP BY result.contentId
ORDER BY result.sortOrder ASC

Error output

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'result.fullURL' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks for any help!

The error message explains it well. There is a group by contentId but this column does not exists in your select. Besides, there is no aggregation (sum, count, min, max) function in your query. Thus group by is useless. Explain to us what you want to achieve. — jose_bacoy, May 04 '18 at 20:18

score 0 · Answer 1 · answered May 04 '18 at 20:18

"Only full group by" means your aggregating queries must group on all non-aggregated fields in the result. It does not matter if they are "hidden" in *, those fields must be grouped by as well.

This query should not even be grouping to begin with; there are no aggregation functions used. If "only full group by" were not the current setting, this query would be "give me one effectively random result, passing the where conditions, for each contentId".

MySQL only_full_group_by error

1 Answers1