“Error”

If you’ve ever joinedmore than a few tables in MySQL (or upgraded MySQL or migrated to another instance), you’ve likely run into the following error:

ERROR 1055 (42000): Expression #2 of SELECT {field} is not in GROUP BY clause and contains nonaggregated column {field} which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thankfully, the answer to the error is implicit in the error itself, and it’s a pretty easy fix:

TLDR:

Ensure that all of the nonaggregated columns in your SELECT clause are also represented in your ORDER BY clause.

Why though?

MySQL, especially in previous versions, was very forgiving or poor SQL standards. Most SQL users would argue that MySQL was too lenient because its forgiving nature made it less performant, less portable, and encouraged poor development practices. This leniency is why many developers have switched to a more standards-compliant RDMS like PostgreSQL. However, sometimes this leniency makes initial development easier and if the product isn’t going into production publicly, does it really matter if the standards are enforced? If it works for your needs, MySQL is fine.

That is, until you decide to upgrade or move your database to an instance that does enforce standards. MySQL allows you to set modes (mysql_mode), some of which prevent certain queries from executing. “ONLY_FULL_GROUP_BY” is one such mode, and it prevents the execution of queries that omit fields that are referenced in SELECT clauses but are not aggregated. This mode is enabled by default on newer versions of MySQL, and discerning developers will enable it on older versions that support it.

 

Fix it

You have two options for fixing a query plagued by this “error”:

  1. Rewrite the query to aggregate columns in the SELECT clause using functions like COUNT, MAX, and MIN
    SELECT video_id, SUM(votes.vote) as tally from talent_videos INNER JOIN votes on talent_videos.video_id = votes.video_id GROUP BY talent_videos.video_id DESC LIMIT 10";
  2. “Aggregate” in the GROUP BY clause by sequentially grouping by all columns in your select, even if they are not actually meant to be grouped.
    SELECT talent_videos.video_id, talent_accounts.slug, talent_videos.title, talent_videos.submit_date, talent_accounts.username, talent_accounts.id as talent_id, talent_accounts.profile_photo, schools.school_name from talent_videos INNER JOIN talent_accounts on talent_videos.talent_id = talent_accounts.id INNER JOIN votes on talent_videos.video_id = votes.video_id INNER JOIN schools on schools.id = talent_accounts.school inner join (select vote_id from votes order by vote_id DESC) vid on vid.vote_id = votes.vote_id GROUP BY talent_videos.video_id, talent_videos.title, talent_videos.submit_date, talent_accounts.username, talent_accounts.id, talent_accounts.profile_photo, schools.school_name ORDER BY tally DESC LIMIT 10";