Page 1 of 1

error filtering with a custom field of type Date

Posted: 27 May 2010, 08:08
by gerald2545
Hi all,
I added a custom field of type Date ("Realisation date") and linked it to all my projects (postgresql 8.1, mantis 1.1.6). I created a bug and assigned a date to "Realisation date"
When filtering (e.g. all bugs which have "Realisation date" after 2010-05-10), I get an error :

Code: Select all

ERROR: operator does not exist: character varying + integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. pour la requête : SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_3 ON mantis_custom_field_string_table_3.bug_id = mantis_bug_table.id AND mantis_custom_field_string_table_3.field_id = 3 WHERE mantis_project_table.enabled = ? AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id in (40, 12, 30, 57, 5, 41, 42, 47, 43, 44, 10, 37, 56, 7, 6, 55, 29, 8, 9, 11, 13, 1, 3, 2, 4, 28, 45) ) AND ( (mantis_custom_field_string_table_3.value+0) BETWEEN 1274911200 AND 2147483647)
.
I then upgraded mantis to 1.2.1....but the problem is still there...

Am I doing something wrong or do you think it's a bug?

I search for such an error in the bugtracker of mantis, but didn't found the same problem.

I know how I can solve this issue by editing the mantis/core/filter_api.php and deleting all occurences of "+0" (it solved the problem in 1.1.6)... Do you know what is the purpose of this "+0"?

Code: Select all

if( $t_def['type'] == CUSTOM_FIELD_TYPE_DATE ) {
                                        switch( $t_filter['custom_fields'][$t_cfid][0] ) {
                                                case CUSTOM_FIELD_DATE_ANY:
                                                        break;
                                                case CUSTOM_FIELD_DATE_NONE:
                                                        array_push( $t_join_clauses, $t_cf_join_clause );
                                                        $t_custom_where_clause = '(( ' . $t_table_name . '.bug_id is null) OR ( ' . $t_table_name . '.value = 0)';
                                                        break;
                                                case CUSTOM_FIELD_DATE_BEFORE:
                                                        array_push( $t_join_clauses, $t_cf_join_clause );
                                                        $t_custom_where_clause = '(( ' . $t_table_name . '.value != 0 AND (' . $t_table_name . '.value+0) < ' . ( $t_filter['custom_fields'][$t_cfid][2] ) . ')';
                                                        break;
                                                case CUSTOM_FIELD_DATE_AFTER:
                                                        array_push( $t_join_clauses, $t_cf_join_clause );
                                                        $t_custom_where_clause = '( (' . $t_table_name . '.value+0) > ' . ( $t_filter['custom_fields'][$t_cfid][1] + 1 );
                                                        break;
                                                default:
                                                        array_push( $t_join_clauses, $t_cf_join_clause );
                                                        $t_custom_where_clause = '( (' . $t_table_name . '.value+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2];
                                                        break;
                                        }
                                }
Thank you for your help
Have a nice day
Gérald

Re: error filtering with a custom field of type Date

Posted: 27 May 2010, 11:10
by atrol
Maybe the +0 should be / or is at least for mySQL a trick to get numerical comparison for the between clause because otherwise comparison would be alphabetical as the value is stores in varchar(255)
But this is just my speculation, I didn't try