Project

General

Profile

Actions

Defect #21182

closed

Project.uniq.visible raises an SQL error under certain conditions

Added by Redmine CRM over 8 years ago. Updated over 8 years ago.

Status:
Closed
Priority:
Normal
Category:
Code cleanup/refactoring
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:

Description

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.


Files

fix_project_uniq_visible.diff (2.51 KB) fix_project_uniq_visible.diff Diff with to trunk(r14853) Redmine CRM, 2015-11-08 21:48
fix_project_uniq_visible.diff (1.72 KB) fix_project_uniq_visible.diff Diff with to trunk(r14853) Redmine CRM, 2015-11-08 21:57
Actions #1

Updated by Redmine CRM over 8 years ago

Sorry, diff somehow got previous commit, probably because I am bad with git diffs, PFA fixed one

Actions #2

Updated by Jean-Philippe Lang over 8 years ago

  • Target version set to 3.2.0
Actions #3

Updated by Jean-Philippe Lang over 8 years ago

  • Subject changed from Database exception if "uniq" and "visible" scopes are applied in that order under certain conditions to Project.uniq.visible raises an SQL error under certain conditions
  • Category set to Code cleanup/refactoring
  • Status changed from New to Closed
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

Fix committed in r14857, with slight changes to the test.
Thanks for pointing this out!

Actions

Also available in: Atom PDF