View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0011963mantisbtdb postgresqlpublic2010-05-28 01:282014-05-16 15:02
Reportergerald2545 Assigned Todregad  
PrioritynormalSeveritycrashReproducibilityalways
Status closedResolutionduplicate 
Product Version1.2.1 
Summary0011963: error filtering with a custom field of type Date
Description

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 :
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)

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"?

Additional Information

source code of core/filter_apip.php to modify to solve this issue (with my installation) :

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;
}
}

TagsNo tags attached.

Relationships

duplicate of 0011279 closeddregad With PostgreSQL filtering for a date custom field yields to an error 

Activities

macarbiter

macarbiter

2011-07-20 05:43

reporter   ~0029214

I think this may have been an attempt at casting the string value to a number for the data comparison that isn't compatible with all databases.

Is there any progress on getting this fixed so that the code is more database independent?

For now, I simply replaced all of those +0 occurrences with the SQL standard CAST (field AS type) syntax to do the cast correctly.
dregad

dregad

2011-07-22 05:30

developer   ~0029267

Last edited: 2011-07-22 07:22

I think you're correct, it's using implicit type conversion to convert the string value stored in the DB table, to a number. This works on MySQL and Oracle for sure. I do not have access to a PostgreSQL environment to test.

The problem with CAST is that while the function itself is standard SQL and available on all supported platforms, AFAIK the data types are different in each RDBMS.

MySQL: SIGNED, UNSIGNED
Oracle: NUMBER, DECIMAL
MSSQL: bigint, int, decimal...

And if you use the wrong type, you get a syntax error. So the function is not as portable as it should be, and using it would force to write RDBMS-specific code.

EDIT: implicit conversion works also on MSSQL (just tested). What happens on PostgreSQL if you execute SELECT '1' + 0 ?
macarbiter

macarbiter

2011-07-22 07:39

reporter   ~0029270

It gives the error as in the description of this issue:
ERROR: operator does not exist: character varying + integer

Since version 8.3 Postgres has had stricter type checking which prohibits this sort of cast. It also applies to type comparisons.

Why not do something like db_prepare_string for this that then casts to any known data type depending on the DB being used?
dregad

dregad

2011-07-22 09:41

developer   ~0029271

Why not do something like db_prepare_string for this

Because if you convert the date to a string, then the date values would then be compared in lexicographical instead of numeric.

In any case I think you are right, the only way would be to make this code RDBMS-specific.
dregad

dregad

2011-07-22 13:09

developer   ~0029273

Same issue is described in 0011279, so I'm marking this resolved/duplicate.
grangeway

grangeway

2014-05-16 15:02

reporter   ~0040531

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul