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:in `execute' /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:in `select' /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:in `select_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:in `find_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:in `find_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:in `method_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:in `method_missing' /Users/justin/projects/app1/vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:209:in `any?'
