Hey folks,
I’ve been upgrading an application from rails 1.2.6 and 2.1, and most
of it has gone smoothly, with one exception. ActiveRecord is
producing the following error on many queries with a PostgreSQL 8.1.5
database:
ActiveRecord::StatementInvalid: PGError: ERROR: for SELECT DISTINCT,
ORDER BY expressions must appear in select list
: SELECT DISTINCT people.* FROM people INNER JOIN subscriptions ON
people.id = subscriptions.person_id WHERE
((subscriptions.publication_id = 1)) ORDER BY
subscriptions.user_order asc
The full stack trace is at the bottom of this email.
This exact query seems to get run in a large number of places where
I’m querying other objects (i.e. not Person) which are associated
with Person. I’ve tried using the postgres, pq, and postgres-pr gems
(one at a time, not all installed at once), with no change. I’ve
attempted to eliminate as many upgrade issues as possible. I’ve
already updated all my plugins (will_paginate, acts_as_versioned,
has_many_polymorphs) to confirmed rails-2.1 compatible versions,
updated the postgres gem, and updated the rails config files.
I see a lot of code in the postgresql connection adapter that seems
specific to postgres 8.3 or greater - but I was under the impression
that the adapter was supposed to support postgres from 7.4 on up.
I’m attempting to avoid upgrading my DB as it’s shared among multiple
applications.
Any hints or ideas would be greatly appreciated.
-Justin
ActiveRecord::StatementInvalid: PGError: ERROR: for SELECT DISTINCT,
ORDER BY expressions must appear in select list
: SELECT DISTINCT people.* FROM people INNER JOIN subscriptions ON
people.id = subscriptions.person_id WHERE
((subscriptions.publication_id = 1)) ORDER BY
subscriptions.user_order asc
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/connection_adapters/abstract_adapter.rb:147:in log' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/connection_adapters/postgresql_adapter.rb:450:inexecute’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/connection_adapters/postgresql_adapter.rb:889:in
select_raw' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/connection_adapters/postgresql_adapter.rb:876:inselect’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/connection_adapters/abstract/database_statements.rb:
7:in select_all_without_query_cache' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/connection_adapters/abstract/query_cache.rb:61:inselect_all’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/base.rb:582:in find_by_sql' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/base.rb:1341:infind_every’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/base.rb:536:in find' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/associations/has_many_through_association.rb:73:infind_target’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/associations/association_collection.rb:259:in
load_target' /Users/justin/projects/app1/vendor/rails/activerecord/lib/ active_record/associations/association_proxy.rb:173:inmethod_missing’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/associations/association_collection.rb:274:in
method_missing_without_paginate' /Users/justin/projects/app1/vendor/plugins/will_paginate-2.2.2/ lib/will_paginate/finder.rb:164:inmethod_missing’
/Users/justin/projects/app1/vendor/rails/activerecord/lib/
active_record/associations/association_collection.rb:209:in `any?’