Query::StatementInvalid raised in dmsf#show when filtering custom fields with PostgreSQL database

[liaham]

Hi,

When applying a filter on a custom field I would expect to get the query result. But instead an exception is raised:

A Query::StatementInvalid occurred in dmsf#show:

  PG::SyntaxError: ERROR:  syntax error at or near "ORDER"
LINE 120: ... ct.id AND   (custom_values.value IN ('Secret'))) ORDER BY s...
                                                               ^
  plugins/redmine_dmsf/app/models/dmsf_query.rb:108:in `rescue in dmsf_count'
  plugins/redmine_dmsf/app/models/dmsf_query.rb:103:in `dmsf_count'
  plugins/redmine_dmsf/app/controllers/dmsf_controller.rb:94:in `block (2 levels) in show'
  plugins/redmine_dmsf/app/controllers/dmsf_controller.rb:92:in `show'
  lib/redmine/sudo_mode.rb:61:in `sudo_mode'

Theses are the request params:

 "dmsf_query"=>
    {:project_id=>1,
     :filters=>
      {"cf_1"=>{:operator=>"=", :values=>["Secret"]},
       "title"=>{:operator=>"~", :values=>[""]}},
     :group_by=>"cf_1",
     :column_names=>[:cf_1, :size, :modified, :version, :workflow, :author],
     :totalable_names=>[],
     :sort=>[["title ASC", "asc"]]},
   "issue_query"=>
    {:project_id=>nil,
     :filters=>{"status_id"=>{:operator=>"o", :values=>[""]}},
     :group_by=>nil,
     :column_names=>nil,
     :totalable_names=>[],
     :sort=>[["id", "desc"]]}}

You can easily reproduce the issue by these steps:

1. Start Redmine with postgres as database.

2. Create a DMS custom field (in my case it was a list field) and mark the checkbox 'Used as a filter'.

3. Navigate into a project with DMS module enabled, upload some files and select a list value for each.

4. Add the DMS custom field to the filter, select a value to filter for and apply the filter.

5. Immediately, the exception should be raised.

This is my environment:

Environment:
  Redmine version                5.0.4.stable
  Ruby version                   2.7.7-p221 (2022-11-24) [x86_64-linux-musl]
  Rails version                  6.1.7
  Environment                    development
  Database adapter               PostgreSQL
  Mailer queue                   ActiveJob::QueueAdapters::AsyncAdapter
  Mailer delivery                smtp
Redmine settings:
  Redmine theme                  Default
SCM:
  Subversion                     1.14.2
  Mercurial                      6.1.1
  Git                            2.36.3
  Filesystem                     
Redmine plugins:
  advanced_plugin_helper         0.3.0
  redmine_dmsf                   3.0.7

Let me know if you need further information to reproduce the issue.

Best Regards, Liane

[picman]

The same problem with MySQL.

[picman]

Could you test the devel branch?

[liaham]

Yes, I can test it this week. Probably on Friday.

[liaham]

Works with PostgreSQL as expected! Thank you very much! :)

[picman]

Thank you for your feedback.