downvoted_commentors model query incompatible with mysql 5.7 defaults

[thomasdziedzic]

I am running mysql 5.7 with default configuration. I'm getting the following errors when running bundle exec rake.

A better explanation of the error can be found here: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql

Finally, you can see the new configuration default in mysql 5.7 at https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html and searching for only_full_group_by

The query in question can be found at https://github.com/lobsters/lobsters/blob/master/app/models/downvoted_commenters.rb#L25-L39

     ActionView::Template::Error:
       Mysql2::Error: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sums.sum_downvotes'; this is incompatible with sql_mode=only_full_group_by:
               select
                 sum_downvotes,
                 stddev(sum_downvotes) as stddev,
                 sum(sum_downvotes) as sum,
                 avg(sum_downvotes) as avg,
                 avg(n_comments) as n_comments,
                 count(*) as n_commenters
               from (
                 select
                   sum(downvotes) as sum_downvotes,
                   count(*) as n_comments
                 from comments join users on comments.user_id = users.id
                 where (comments.created_at >= '2018-07-14 19:42:29 -0500')
                 GROUP BY comments.user_id
               ) sums;

     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activesupport-5.2.0/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activesupport-5.2.0/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activesupport-5.2.0/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activesupport-5.2.0/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:196:in `execute_and_free'
     # /home/thomas/.rvm/gems/ruby-2.3.3/gems/activerecord-5.2.0/lib/active_record/connection_adapters/mysql/database_statements.rb:33:in `exec_query'
     # ./app/models/downvoted_commenters.rb:24:in `block in aggregates'

[pushcx]

Huh, yeah. I can see how I left it in as a I built up that query, but I can't see why it doesn't error. I'm using MariaDB 10.1.35 locally and we're on 10.0.34 in prod, fwiw.

Thanks for catching and fixing this.