Redmine

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 `<top (required)>'
    from bin/rails:4:in `require'
    from bin/rails:4:in `<main>'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.