View Issue Details

WikiRelated ChangesetsJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0016584mantisbtfilterspublic2013-11-07 05:042014-12-08 00:34
Reporterkensonman Assigned Todregad  
PriorityurgentSeveritycrashReproducibilityalways
Status closedResolutionfixed 
PlatformChromeOSUbuntu Linux + PostgreSQL 
Product Version1.2.15 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0016584: Error when query bug by custom-field (date) in postgresql
Description

The error message are prompted.

Error message:
ERROR: operator does not exist: character varying + integer
LINE 1: ...AND ( ( (mantis_custom_field_string_table_20.value+0) > 1383...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.，Query SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_20 ON mantis_bug_table.id = mantis_custom_field_string_table_20.bug_id AND mantis_custom_field_string_table_20.field_id = 20 WHERE mantis_project_table.enabled = ? AND ( mantis_bug_table.project_id = 3 ) AND ( ( (mantis_custom_field_string_table_20.value+0) > 1383839999) )

Steps To Reproduce
  1. Setup the system using the postgressql database;
  2. Create a custom field in date;
  3. Assign that custom field to specified project;
  4. Go to "View Issues";
  5. Expends the filter panel and filter Issue with that custom-filed;
  6. Click "Apply Filter" and the Error Message prompted;
  7. The error message are keeping there until you clear the session;
Additional Information

I had fixed those issue by below modification:

@@ -1890,19 +1890,19 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
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)';
  • $t_custom_where_clause = '(( ' . $t_table_name . '.bug_id is null) OR ( CAST(COALESCE(NULLIF(' . $t_table_name . '.value,\'\'),\'0\') AS INT) = 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] ) . ')';
  • $t_custom_where_clause = '(( ' . $t_table_name . '.value != 0 AND (CAST(COALESCE(NULLIF(' . $t_table_name . '.value,\'\'),\'0\') AS INT)+0) < ' . ( $t_filter
    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 );
  • $t_custom_where_clause = '( (CAST(COALESCE(NULLIF(' . $t_table_name . '.value,\'\'),\'0\') AS INT)+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
  • $t_custom_where_clause = '( (CAST(COALESCE(NULLIF(' . $t_table_name . '.value,\'\'),\'0\') AS INT)+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . '
    break;
    }
TagsNo tags attached.

Relationships

Relationship GraphDependency Graph
related to 0007849 closedgiallu filters on custom date field causes SQL error 

Activities

kensonman

kensonman

2013-11-07 05:06

reporter   ~0038455

I'm trying to fix this issue according to SQL92, so it should be work on both postgresql and mysql.
dregad

dregad

2013-11-08 08:25

developer   ~0038511

The only problem is that while the SQL CAST() function is indeed SQL standard and does exist in the various RDBMS, the available types differ.

Using your example, MySQL does not allow 'INT' [1]; I think the most portable type to use would be DECIMAL, however that requires MySQL 5.08 or later (which conflicts with our current minimum requirements).

[1] http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast
kensonman

kensonman

2013-11-11 01:39

reporter   ~0038526

According to the below linkage[1], the INT should be supported in MySQL 4.1.

For better backward compatibility, may be NUMERIC is the good choice.

[1]http://dev.mysql.com/doc/refman/4.1/en/integer-types.html
dregad

dregad

2013-11-11 03:14

developer   ~0038527

Last edited: 2013-11-11 03:14

As per my link, neither INT nor NUMERIC are supported by the CAST function (EDIT: in MySQL). You can used SIGNED [INTEGER] or UNSIGNED [INTEGER], but that in turn is not accepted by other RDBMS.

Related Changesets

MantisBT: master d46001bd

2013-11-08 03:26

dregad


Details Diff		 Fix error with PostgreSQL when filtering on date custom field

As PostreSQL tries to add 0 to a string, it throws an error as it tries
to operate on different data types.

I can only guess that the +0 was a hack to convert type from string to
integer on MySQL.

Implementing solution proposed by user kensonman, using standard SQL
functions CAST, COALESCE and NULLIF to achieve a clean and portable
conversion to numeric (this assumes that the data stored in the
custom_fields_string table 'value' column is indeed numeric; if not we
would get other errors or warnings)

Note: this requires bumping MySQL minimum requirement to 5.08, since
DECIMAL type used in CAST was only added in that release.

Fixes 0016584		 Affected Issues
0016584
mod - core/filter_api.php Diff File
mod - docbook/Admin_Guide/en-US/Installation.xml Diff File