MySQL: contains nonaggregated column error when using .group() | Rails 5.2

Question

I'm working on a Rails 5.2 project and trying to groupan ActiveRecord::Relation by a field in the table:

The below in the rails console returns an error:

2.4.0 :015 > Post.group(:published)
  Post Load (3.9ms)  SELECT  `posts`.* FROM `posts` GROUP BY `posts`.`published` LIMIT 11
ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'app_dev.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT  `posts`.* FROM `posts` GROUP BY `posts`.`published` LIMIT 11

But, adding .sum() runs correctly without an error...

> Post.group(:published).sum(:views)

Is there an apparent reason the first query would fail like that, and not the second?

In schema.rb

create_table "post", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
  t.string "title", null: false
  t.text "body", null: false
  t.boolean "published", null: false, default: false
  t.bigint "views", null: false, default: 0
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

Thanks

What is the SQL query for `Post.group(:published).sum(:views)`? — Pramod Shinde, Feb 06 '19 at 06:53
The error is clear you cannot use group by in this way. group by expects some aggregation which you done't have and that all the items in the select clause are also in the group by clause - mysql will allow less in the group by if it detects functional dependency.see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html but the result will be non deterministic. BTW a limit clause without an order by may not return what you want. — P.Salmon, Feb 06 '19 at 07:37
I think this will help you - https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc — Pramod Shinde, Feb 06 '19 at 07:48

score 0 · Answer 1 · answered Jan 24 '20 at 13:38

The problem in the first case is that you're running mysql in only_full_group_by mode, which means you can't have columns in your select statement that don't depend from the ones in the group by clause:

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

If you look into the second query generated by Rails you will see that the sum method adds to the select list the aggregate function SUM(views) and restrict the other attributes to only the one inside the group method, resulting in something like this:

SELECT SUM(post.views) AS sum_views, post.published AS post_published FROM post GROUP BY post.published

You could have the same effect (although with a different presentation) if you use:

Post.select("SUM(views), published").group(:published).as_json

MySQL: contains nonaggregated column error when using .group() | Rails 5.2

1 Answers1