Project

General

Profile

Actions
Copy link

Defect #8371

closed

MySQL error when filtering a custom field using the REST api

Added by Bruno Bigras almost 13 years ago. Updated over 12 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
REST API
Target version:
1.3.0
Start date:
2011-05-13
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:

Description

MySQL 5.1.41
Redmine 1.1.3
ruby 1.8.7
rails 2.3.5

I created a custom field named 'sha1' (text format), 0 to 40 length.

I tested it with a dumb value 'aaa' and it worked fine with http://myhost/redmine/issues.xml?project_id=5&cf_1=aaa

I had an error when I tried to use it with a real sha1 hash.

I was able to reproduce the problem when trying to filter with a value of 'c' but it worked with 'a' and 'b'.
http://myhost/redmine/issues.xml?project_id=5&cf_1=a <-- works
http://myhost/redmine/issues.xml?project_id=5&cf_1=b <-- works
http://myhost/redmine/issues.xml?project_id=5&cf_1=c <-- doesn't works

I get from the log:

Processing IssuesController#index to xml (for 10.1.10.240 at 2011-05-13 13:43:19) [GET]
  Parameters: {"format"=>"xml", "project_id"=>"5", "action"=>"index", "controller"=>"issues", "cf_1"=>"c"}
Query::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right
syntax to use near ') AND projects.id = 5 AND projects.status=1 AND 
projects.id IN (SELECT em.projec' at line 1:

There's no condition after the where at line 14. (note that I indented the query myself).

SELECT count(DISTINCT `issues`.id) AS count_all
FROM `issues`
LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id
LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id
WHERE
    ((issue_statuses.is_closed=0) AND
    issues.id IN (
        SELECT issues.id
        FROM issues
        LEFT OUTER JOIN custom_values ON
            custom_values.customized_type='Issue' AND
            custom_values.customized_id=issues.id AND
            custom_values.custom_field_id=1
        WHERE 
    ) AND
    projects.id = 5 AND
    projects.status=1 AND
    projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))

Files

Download all files
2013_fix_custom_field_filter.patch (770 Bytes) 2013_fix_custom_field_filter.patch Bruno Bigras, 2011-05-19 16:37
short_filters_regexps.patch (4.21 KB) short_filters_regexps.patch Etienne Massip, 2011-05-20 15:23
filter_out_illegal_query_filter_values.patch (1.98 KB) filter_out_illegal_query_filter_values.patch Etienne Massip, 2011-10-19 20:20
Actions
Copy link

Also available in: Atom PDF