ActiveRecord::StatementInvalid caused by "sorted" scope

[eyewritecode]

I upgraded to Redmine 4.0 with:

• rails 5.2.0
• ruby 2.4.1

But i get this error when i try to render issues/:id.

I tried to troubleshoot this and it seems like it's caused by the sorted method call added in this commit

ActiveRecord::StatementInvalid at /issues/311
PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...atus" = $1 AND (members.project_id = 12) ORDER BY COALESCE(N...
                                                             ^
: SELECT DISTINCT "users".* FROM "users" INNER JOIN "members" ON "members"."user_id" = "users"."id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "users"."status" = $1 AND (members.project_id = 12) ORDER BY COALESCE(NULLIF(users.displayname,''), users.login), users.id

Application Frames All Frames
#<Class:0x007fe49f28fbc8>#issue_author_options_for_select
plugins/additionals/app/helpers/additionals_issues_helper.rb, line 7
#<Class:0x007fe49f28fbc8>#_d32f8ab79157f591ea6617b483e289f7
plugins/additionals/app/views/issues/_change_author.html.slim, line 2

using

authors = project.users.sort

works without problem

[alexandermeindl]

Thanks for reporting this! I suppose, you use the addtionals macro {{members}}. Do you use any parameters to this macro? Which Redmine version to you use exactly? I cannot reproduce this problem in my environment with Redmine 4.0.2. Which database type and version do you use? MySQL 5.7 and PostgreSQL 9.6 I checked with no problems.

[alexandermeindl]

In your query there is an column named users.displayname. This is not part of Redmine or additionals. Which other plugins do you use? I suppose, it is a compatibility problem with the plugin, which adds this column.

[eyewritecode]

Thanks for getting back to me. I'm using PostgreSQL 11.2 and this is my redmine information

Environment:
  Redmine version                4.0.0.stable.17734
  Ruby version                   2.4.1-p111 (2017-03-22) [x86_64-darwin16]
  Rails version                  5.2.0
  Environment                    development
  Database adapter               PostGIS
  Mailer queue                   ActiveJob::QueueAdapters::AsyncAdapter
  Mailer delivery                smtp
SCM:
  Subversion                     1.10.0
  Git                            2.17.2
  Filesystem                     
Redmine plugins:
  additionals                    2.0.19
  etc... 

Yeah you're right, the displayname comes from our internal plugin that adds that column for "privacy" reasons and i guess that's what causes the conflict.

Any idea on how i should go about this?!!

[alexandermeindl]

Best solution would be, if you can drop DISTINCT. DISTINCT is very often a problem, you overwrite a query, which is used from other plugins, too.

[alexandermeindl]

Can I do anything for you regarding this issue or can we close it?

[eyewritecode]

I'll go ahead and close it... I found a workaround on our plugin!