search

maxrossello / redmine_extended_watchers

Grant additional issue and project view permissions to watcher users
GNU General Public License v3.0
44 stars 20 forks source link

cant filter tickets by assignee in extended mode #36

Closed urbaxy closed 2 years ago

urbaxy commented 2 years ago

Greetings. When I try to add an assignee or author filter to the ticket list in extended mode, the field just doesnt appear. Users who should have permission to see tickets as extended watchers have a yellow exclamation sign and dont see the tickets or receive any notifications. Im using Debian 10 and MS SQLServer 2019. I testet this behavior with mysql and it worked well. The log says:

ActiveRecord::StatementInvalid (TinyTds::Error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.: SELECT [projects].* FROM [projects] WHERE (projects.lft > 23 AND projects.rgt < 32) AND (( projects.status <> 9 OR (projects.id IN (SELECT [projects].[id] FROM [projects] INNER JOIN [issues] ON [issues].[project_id] = [projects].[id] WHERE (projects.lft > 23 AND projects.rgt < 32) AND (( projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking') OR ( (issues.id IN (SELECT [issues].[id] FROM [issues] INNER JOIN [watchers] ON [watchers].[watchable_id] = [issues].[id] AND [watchers].[watchable_type] = N'Issue' WHERE [watchers].[user_id] = 5)) AND ( 1=1 )) ) ) ORDER BY [projects].[lft] ASC)) )) ORDER BY [projects].[lft] ASC):

grafik

grafik

Environment: Redmine version 4.2.2.stable Ruby version 2.6.5-p114 (2019-10-01) [x86_64-linux] Rails version 5.2.6 Environment production Database adapter SQLServer Mailer queue ActiveJob::QueueAdapters::AsyncAdapter Mailer delivery smtp SCM: Git 2.20.1 Filesystem
Redmine plugins: issue_recurring 1.6 issuecloser 0.0.1 redmine_auto_assign_group 0.1.1 redmine_callout_macro 1.0.0 redmine_checklists 3.1.18 redmine_cozy_wiki_macros 0.3.0 redmine_custom_workflows 1.0.2 redmine_dmsf 2.4.7 redmine_editauthor 0.11.0 redmine_extended_watchers 4.2.2 redmine_image_clipboard_paste 3.3.0 redmine_issue_dynamic_edit 0.7.2 redmine_issue_templates 1.1.0 redmine_lightbox2 0.5.1 redmine_related_wiki 1.0.0 redmine_theme_changer 0.4.0 redmine_wiki_extensions 0.9.2 redmine_wysiwyg_editor 0.17.0 redmineup_tags 2.0.11

I hope you can fix this issue, because this plugin makes Redmine how it should be. Thanks for your help. Urbaxy

maxrossello commented 2 years ago

I'm sorry but I don't own a license for MS SQL. The max I can do is to test a proposed fix for regression against other databases and deliver it.

I anyway also doubt that the double " ORDER BY [projects].[lft] ASC" towards the end of the query is responsibility of this plugin.

I suggest you to try removing plugins selectively until you find the incompatibility combination. If you find out that a plain Redmine without plugins works, and just adding this plugin (and nothing else) doesn't, then please post the reported original SQL query (without plugin) and the full stack in the error log, so that at the least I can try to imagine where the problem could be.

maxrossello commented 2 years ago

Closing the issue for no response given. Feel free to reopen. Thanks.