https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292011-12-05T08:59:14ZRedmineRedmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344302011-12-05T08:59:14ZEtienne Massip
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Confirmed</i></li><li><strong>Target version</strong> set to <i>1.3.0</i></li></ul><p>Your error triggers with the "equals" operator.</p>
<p>Using the "<=" operator triggers a different error:<br /><pre>
Processing IssuesController#index (for 10.132.21.138 at 2011-12-05 09:51:59) [GET]
Parameters: {"v"=>{"cf_1"=>["100"]}, "op"=>{"cf_1"=>"<="}, "group_by"=>"", "project_id"=>"prctwa", "set_filter"=>"1", "c"=>["tracker", "status", "priority", "subject", "assigned_to", "updated_on", "done_ratio", "cf_6"], "action"=>"index", "f"=>["cf_1", ""], "controller"=>"issues"}
Query::StatementInvalid: PGError: ERREUR: syntaxe en entrée invalide pour le type numeric : « »
: SELECT count(DISTINCT "issues".id) AS count_all FROM "issues" LEFT OUTER JOIN "projects" ON "projects".id = "issues".project_id LEFT OUTER JOIN "issue_statuses" ON "issue_statuses".id = "issues".status_id WHERE (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 CAST(custom_values.value AS decimal(60,3)) <= 100.0) AND projects.id = 1) AND (projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))
Rendering template within layouts/base
Rendering common/error (500)
</pre></p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344462011-12-05T15:58:53ZJames Kyle
<ul></ul><p>Etienne Massip wrote:</p>
<blockquote>
<p>Your error triggers with the "equals" operator.</p>
<p>Using the "<=" operator triggers a different error:<br />[...]</p>
</blockquote>
<p>That's the one. It looks (to me) like the query doesn't account for empty strings, which CAST doesn't know how to convert.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344552011-12-05T20:50:32ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Status</strong> changed from <i>Confirmed</i> to <i>Resolved</i></li></ul><p>Quick fix committed in <a class="changeset" title="Fixed: error when filtering by numeric custom field with postgresql (#9719)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/8098">r8098</a>.</p>
<p>I think we should split the custom_values.value column into several typed columns (eg. text_value, int_value, float_value, date_value, bool_value) for next major release.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344592011-12-05T22:37:42ZEtienne Massip
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>I think we should split the custom_values.value column into several typed columns (eg. text_value, int_value, float_value, date_value, bool_value) for next major release.</p>
</blockquote>
<p>I like the idea of having a unique value column instead of one per type; each time I see some table with one column per type, its content is ugly; why not some xml data which now belongs to standard SQL?</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344642011-12-06T04:42:17ZJames Kyle
<ul></ul><p>Thanks for this incredibly timely fix!</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344692011-12-06T08:47:35ZEtienne Massip
<ul></ul><p>Etienne Massip wrote:</p>
<blockquote>
<p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>I think we should split the custom_values.value column into several typed columns (eg. text_value, int_value, float_value, date_value, bool_value) for next major release.</p>
</blockquote>
<p>I like the idea of having a unique value column instead of one per type; each time I see some table with one column per type, its content is ugly; why not some xml data which now belongs to standard SQL?</p>
</blockquote>
XML seems like a odd idea and is not supported by SQLite3 anyway.<br />Being used to it for a long time, I don't like the idea of having a column per type because of the dirty contents, but I must admit it would solve both issues:
<ul>
<li>performance because of in-query transformation of value</li>
<li>query crash because of wrong value type stored in value column (but this could also be worked out with an additional 'value type' column)</li>
</ul>
<p>The second issue will come back soon as a defect since it happens when a custom field used to store literals is switched from any type to Number.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344822011-12-06T15:58:03ZJames Kyle
<ul></ul><blockquote>
<p>but this could also be worked out with an additional 'value type' column</p>
</blockquote>
<p>This makes the most sense to me. If you add a column for each type any addition of types require changing the database schema in addition to any code associated with displaying, querying, etc. those types. If a 'value_type' column is used, additional types only require modification of relevant code...often just the addition of a case statement.</p>
<p>If a default is provided for unknown types, instead of crashing the application it could render the page but insert something like "unknown type" in the value column. This would seem to require less developer and maintenance resources.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344842011-12-06T17:53:19ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>Thanks for your feedbacks</p>
<p>James Kyle wrote:</p>
<blockquote>
<p>If you add a column for each type any addition of types require changing the database schema in addition to any code associated with displaying, querying, etc. those types.</p>
</blockquote>
<p>I didn't mean 1 column per custom field type but 1 column for each data type: integer, float, text, date, bool. These data types can be reused by different custom field types.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=344852011-12-06T18:04:14ZEtienne Massip
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>I didn't mean 1 column per custom field type but 1 column for each data type: integer, float, text, date, bool. These data types can be reused by different custom field types.</p>
</blockquote>
<p>We could use the same column for numerics so converting a CF from float to integer is not destructive.</p> Redmine - Defect #9719: Filtering by numeric custom field types broken after update to masterhttps://www.redmine.org/issues/9719?journal_id=345582011-12-07T21:41:38ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul>