Actions
Defect #28232
closed
Sql error when adding custom field in issues list
Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Custom fields
Target version:
-
Start date:
Due date:
% Done:
0%
Estimated time:
Resolution:
Invalid
Affected version:
Description
Hello,
i have this error, when i try to add custom field in issue list
Started GET "/projects/helpdesk/issues?utf8=%E2%9C%93&set_filter=1&sort=id%3Adesc&f%5B%5D=status_id&op%5Bstatus_id%5D=o&f%5B%5D=&c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=author&c%5B%5D=subject&c%5B%5D=assigned_to&c%5B%5D=priority&c%5B%5D=updated_on&c%5B%5D=done_ratio&c%5B%5D=cf_8&group_by=&t%5B%5D=" for 192.168.4.70 at 2018-02-22 10:36:56 +0300
Processing by IssuesController#index as HTML
Parameters: {"utf8"=>"✓", "set_filter"=>"1", "sort"=>"id:desc", "f"=>["status_id", ""], "op"=>{"status_id"=>"o"}, "c"=>["tracker", "status", "author", "subject", "assigned_to", "priority", "updated_on", "done_ratio", "cf_8"], "group_by"=>"", "t"=>[""], "project_id"=>"helpdesk"}
Current user: a.sinushin (id=46)
Query::StatementInvalid: PG::SyntaxError: ОШИБКА: ошибка синтаксиса (примерное положение: ".")
LINE 4: ... cv.cnt,
^
: SELECT custom_values.*, cv.cnt, cv.field_format FROM "custom_values" INNER JOIN
(
SELECT cv.id
cv.cnt,
cv.field_format
FROM
(
SELECT cv.id,
cv_m.cnt,
cf.field_format,
case when cf.multiple = 't' and cf.acl_trim_multiple = 't' 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 (8) and cv.customized_id IN (1166,1160,1159,1157,1155,1151,1148,923) 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 (8)
and i.id IN (1166,1160,1159,1157,1155,1151,1148,923)
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
Rendered common/error.html.erb within layouts/base (0.3ms)
How can i patch it?
Updated by Go MAEDA about 6 years ago
Please try again without plugins if you use plugins.
Updated by Maickel Neitzke over 5 years 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".
Updated by Go MAEDA over 5 years ago
- Status changed from New to Closed
- Resolution set to Invalid
This is a plugin issue, not Redmine. Please contact the author of the plugin.
Actions