Defect #27397

issues with custom field sql query error

Added by Evgeniy Fedorov 11 months ago. Updated about 1 month ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Custom fields
Target version:-
Resolution:Invalid Affected version:3.4.3

Description

When adding custom field in issues list,
USING MS SQL 2016

generates query 
SELECT custom_values.*, cv.cnt, cv.field_format FROM [custom_values] INNER JOIN
    (
ERROR 1 ---    SELECT cv.id
            cv.cnt,
            cv.field_format
    FROM
    (
    SELECT cv.id,
            cv_m.cnt,
            cf.field_format,
            case when cf.multiple = 1 and cf.acl_trim_multiple = 1 then 1 else 0 end as mlt,
ERROR 2 ---            ROW_NUMBER() OVER (PARTITION BY i.id, cv.custom_field_id, ORDER BY i.id, cv.custom_field_id, cv.id) as row_number
    FROM custom_values cv
            INNER JOIN custom_fields cf on cf.id = cv.custom_field_id
            INNER JOIN issues i on i.id = cv.customized_id
            INNER JOIN (SELECT COUNT(1) as cnt, cv.custom_field_id, cv.customized_id FROM custom_values cv WHERE cv.customized_type = ''Issue'' and cv.custom_field_id IN (1) and cv.customized_id IN (1015,1012,1010,1005,49,48,46,44,41,29,28,27,23,22,18,13,11,10,9,8,7) GROUP BY cv.custom_field_id, cv.customized_id) cv_m on cv_m.custom_field_id = cf.id and cv_m.customized_id = i.id
    WHERE cv.customized_type = ''Issue''
        and cv.custom_field_id IN (1)
        and i.id IN (1015,1012,1010,1005,49,48,46,44,41,29,28,27,23,22,18,13,11,10,9,8,7)
ERROR 3 ---    ORDER BY i.id, cv.custom_field_id, cv.id
    ) cv
    WHERE cv.mlt = 0 OR cv.row_number <= 3
    ) cv on cv.id = custom_values.id  ORDER BY [custom_values].[customized_id] ASC, [custom_values].[custom_field_id] ASC, [custom_values].[id] ASC

with 3 errors. 

1) no comma after id
2) ROW_NUMBER() OVER (PARTITION BY i.id, cv.custom_field_id, ORDER BY i.id, cv.custom_field_id, cv.id) as row_number
the comma doesn't be before order by
3) ORDER BY
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.

History

#1 Updated by Evgeniy Fedorov 11 months ago

  • Status changed from New to Resolved

find plugin that generates this error ^(

#2 Updated by Toshi MARUYAMA 11 months ago

  • Description updated (diff)

#3 Updated by Toshi MARUYAMA 11 months ago

  • Status changed from Resolved to Closed
  • Resolution set to Invalid

#4 Updated by Maickel Neitzke about 1 month ago

I was with the same error here.

In my case, the problem happened because of the "a commom libs" plugin (https://www.redmine.org/plugins/a_common_libs) with postgre database. This plugin is necessary for others plugins, to me was the usability.

To solve the problem you need to edit file plugins/a_common_libs/lib/acl/patches/models/issue_query_patch.rb or update the plugin.

 when :postgresql, :sqlserver
                  cvs = CustomValue.joins("INNER JOIN
                                                (
                                                  SELECT cv.id,
                                                         cv.cnt,
                                                         cv.field_format
                                                  FROM
                                                  (
                                                    SELECT cv.id,
                                                           cv_m.cnt,
                                                           cf.field_format,
                                                           case when cf.multiple = #{Issue.connection.quoted_true} and cf.acl_trim_multiple = #{Issue.connection.quoted_true} then 1 else 0 end as mlt,
                                                           ROW_NUMBER() OVER (PARTITION BY i.id, cv.custom_field_id ORDER BY i.id, cv.custom_field_id, cv.id) as row_number
                                                    FROM custom_values cv
                                                         INNER JOIN custom_fields cf on cf.id = cv.custom_field_id
                                                         INNER JOIN issues i on i.id = cv.customized_id
                                                         INNER JOIN (SELECT COUNT(1) as cnt, cv.custom_field_id, cv.customized_id FROM custom_values cv WHERE cv.customized_type = 'Issue' and cv.custom_field_id IN (#{query_cfs.join(',')}) and cv.customized_id IN (#{ids.join(',')}) GROUP BY cv.custom_field_id, cv.customized_id) cv_m on cv_m.custom_field_id = cf.id and cv_m.customized_id = i.id
                                                    WHERE cv.customized_type = 'Issue'
                                                      and cv.custom_field_id IN (#{query_cfs.join(',')})
                                                      and i.id IN (#{ids.join(',')})
                                                    ORDER BY i.id, cv.custom_field_id, cv.id
                                                  ) cv
                                                  WHERE cv.mlt = 0 OR cv.row_number <= 3
                                                ) cv on cv.id = #{CustomValue.table_name}.id
                                          ").order(:customized_id, :custom_field_id, :id).select("#{CustomValue.table_name}.*, cv.cnt, cv.field_format")
              end

Add the coma after "cv.id" and remove the coma before "ORDER BY".

Also available in: Atom PDF