Skip to content

Example in README.markdown generates invalid SQL #86

Closed
dkam opened this Issue Sep 28, 2011 · 2 comments

3 participants

@dkam
dkam commented Sep 28, 2011

Hi guys,
The following example given in the documentation generates invalid SQL.

GROUP BY is called group:
   users.group(users[:name]) # => SELECT * FROM users GROUP BY name

MySQL will execute this, by PostgreSQL will complain that:

ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT * FROM users GROUP BY name

Cheers,
Dan

@parkerl
parkerl commented Dec 11, 2012

I have confirmed this in

* arel (3.0.2)

Installed gems include:

 * actionmailer (3.2.3)
  * actionpack (3.2.3)
  * activemodel (3.2.3)
  * activerecord (3.2.3)
  * activeresource (3.2.3)
  * activesupport (3.2.3)
  * arel (3.0.2)
  * builder (3.0.4)
  * bundler (1.0.22)
  * coffee-rails (3.2.2)
  * coffee-script (2.2.0)
  * coffee-script-source (1.4.0)
  * erubis (2.7.0)
  * execjs (1.4.0)
  * hike (1.2.1)
  * i18n (0.6.1)
  * journey (1.0.4)
  * jquery-rails (2.1.4)
  * json (1.7.5)
  * mail (2.4.4)
  * mime-types (1.19)
  * multi_json (1.5.0)
  * pg (0.14.1)
  * polyglot (0.3.3)
  * rack (1.4.1)
  * rack-cache (1.2)
  * rack-ssl (1.3.2)
  * rack-test (0.6.2)
  * rails (3.2.3)
  * railties (3.2.3)
  * rake (10.0.2)
  * rdoc (3.12)
  * sass (3.2.3)
  * sass-rails (3.2.5)
  * sprockets (2.1.3)
  * thor (0.14.6)
  * tilt (1.3.3)
  * treetop (1.4.12)
  * tzinfo (0.3.35)
  * uglifier (1.3.0)

Console output:

1.9.2-p180 :001 > User.group(:name).to_sql
 => "SELECT \"users\".* FROM \"users\"  GROUP BY name" 
1.9.2-p180 :002 > User.group(:name).all

 User Load (1.0ms)  SELECT "users".* FROM "users" GROUP BY name
PG::Error: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "users".* FROM "users"  GROUP BY name
               ^
: SELECT "users".* FROM "users"  GROUP BY name
ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "users".* FROM "users"  GROUP BY name
               ^
: SELECT "users".* FROM "users"  GROUP BY name
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1139:in `async_exec'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1139:in `exec_no_cache'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:663:in `block in exec_query'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.3/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:662:in `exec_query'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1234:in `select'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/querying.rb:38:in `block in find_by_sql'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/explain.rb:40:in `logging_query_plan'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/querying.rb:37:in `find_by_sql'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/relation.rb:171:in `exec_queries'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/relation.rb:160:in `block in to_a'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/explain.rb:33:in `logging_query_plan'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/relation.rb:159:in `to_a'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.2.3/lib/active_record/relation/finder_methods.rb:159:in `all'
    from (irb):2
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.3/lib/rails/commands/console.rb:47:in `start'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.3/lib/rails/commands/console.rb:8:in `start'
    from /Users/parker/.rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.3/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
@parkerl
parkerl commented Dec 14, 2012

This issue is related to functionality or lack there of in both arel and activerecord.

If you look in the select manager on on line 86 where the group iss added you can see that the column of the grouping is not added to those in the select list.

https://github.com/rails/arel/blob/master/lib/arel/select_manager.rb

Also, in activerecord's query_methods in the build_arel method where the groupings are added the columns are not added to the list of selects by default. This means that when build_select is called line 834 is hit which adds * and results in invalid SQL.

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation/query_methods.rb

Gien the above it would seem that rather than changing functionality it is the developer's responsibility to use well formed arel. With that in mind the README should be changed to be more exemplary.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.