Hi! Recently the issue in Redmine core was discovered by our team. To reproduce it just run @Project.uniq.visible@ in console having at least single project and AnonymousGroup added to it as member.

I have tested it under

Environment:
  Redmine version                3.1.1.devel
  Ruby version                   2.2.2-p95 (2015-04-13) [x86_64-darwin14]
  Rails version                  4.2.4
  Environment                    development
  Database adapter               PostgreSQL
SCM:
  Subversion                     1.7.20
  Git                            2.4.9
  Filesystem
Redmine plugins:
  no plugin installed

Stack trace:

PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ..."user_id" = $1 AND (projects.status<>9)  ORDER BY projects.n...
                                                             ^
: SELECT DISTINCT "projects".id FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = $1 AND (projects.status<>9)  ORDER BY projects.name
ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ..."user_id" = $1 AND (projects.status<>9)  ORDER BY projects.n...
                                                             ^
: SELECT DISTINCT "projects".id FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = $1 AND (projects.status<>9)  ORDER BY projects.name
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:641:in `prepare'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:641:in `prepare_statement'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `exec_cache'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:589:in `execute_and_clear'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract/database_statements.rb:351:in `select'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation/calculations.rb:180:in `pluck'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/associations/collection_association.rb:58:in `ids_reader'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/associations/builder/collection_association.rb:66:in `project_ids'
    from /Users/k41n/redmine_development/app/models/user.rb:568:in `block in projects_by_role'
    from /Users/k41n/redmine_development/app/models/user.rb:568:in `reject!'
    from /Users/k41n/redmine_development/app/models/user.rb:568:in `projects_by_role'
    from /Users/k41n/redmine_development/app/models/project.rb:202:in `allowed_to_condition'
    from /Users/k41n/redmine_development/app/models/project.rb:167:in `visible_condition'
... 1 levels...
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/scoping/named.rb:155:in `call'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/scoping/named.rb:155:in `block (2 levels) in scope'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation.rb:302:in `scoping'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/scoping/named.rb:155:in `block in scope'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation/delegation.rb:94:in `public_send'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation/delegation.rb:94:in `block in method_missing'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation.rb:302:in `scoping'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/activerecord-4.2.4/lib/active_record/relation/delegation.rb:94:in `method_missing'
    from (irb):1
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/railties-4.2.4/lib/rails/commands/console.rb:110:in `start'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/railties-4.2.4/lib/rails/commands/console.rb:9:in `start'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/railties-4.2.4/lib/rails/commands/commands_tasks.rb:68:in `console'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/railties-4.2.4/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
    from /Users/k41n/.rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/railties-4.2.4/lib/rails/commands.rb:17:in `'
    from bin/rails:4:in `require'
    from bin/rails:4:in `
'irb(main):002:0>

Problem was initially reported by https://bitbucket.org/ViruZzz/ who is developing plugins for Redmine. One of customers complained about plugin raising exception above under certain circumstances. After investigation I think I found the problem.

Plugin contains model named @Contact@ having following association:

has_and_belongs_to_many :projects, lambda { uniq }

Sometimes it uses @visible@ scope to filter out projects visible to current user, giving effectively chain of @uniq@ and @visible@. That causes exception.

After taking a dive in depth of ActiveRecord I discovered that Principal model sets order for projects table (https://github.com/redmine/redmine/blob/master/app/models/principal.rb#L32) which gets merged to entire relation conflicting with uniq which adds @SELECT DISTINCT(id)@ to query.

That entire case is quite rare, it happens only if AnonymousGroup is added as member to some public project.

However, I made a patch, removing that order by name from principal association. I have totally no idea who and why added it there, maybe it is something vital, maybe not. However there is issue and I am attaching one of the solutions which works for me.

All tests are passing.