Your error triggers with the "equals" operator.

Using the "<=" operator triggers a different error:
<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>
--------------------------------------------------------------------------------
Etienne Massip wrote:
> Your error triggers with the "equals" operator.
>
> Using the "<=" operator triggers a different error:
> [...]

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.
--------------------------------------------------------------------------------
Quick fix committed in r8098.

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.
--------------------------------------------------------------------------------
Jean-Philippe Lang wrote:
> 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.

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?

--------------------------------------------------------------------------------
Thanks for this incredibly timely fix!
--------------------------------------------------------------------------------
Etienne Massip wrote:
> Jean-Philippe Lang wrote:
> > 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.
>
> 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?

XML seems like a odd idea and is not supported by SQLite3 anyway.
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:
* performance because of in-query transformation of value
* query crash because of wrong value type stored in value column (but this could also be worked out with an additional 'value type' column)

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.

--------------------------------------------------------------------------------
> but this could also be worked out with an additional 'value type' column

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.

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.
--------------------------------------------------------------------------------
Thanks for your feedbacks

James Kyle wrote:
> 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.

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.
--------------------------------------------------------------------------------
Jean-Philippe Lang wrote:
> 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.

We could use the same column for numerics so converting a CF from float to integer is not destructive.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------