View Issue Details

IDProjectCategoryView StatusLast Update
0016584mantisbtfilterspublic2014-12-08 00:34
ReporterkensonmanAssigned Todregad 
PriorityurgentSeveritycrashReproducibilityalways
Status closedResolutionfixed 
PlatformChromeOSUbuntu Linux + PostgreSQLOS Version
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

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

View 2 revisions

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 08:26:05

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
mod - core/filter_api.php Diff File
mod - docbook/Admin_Guide/en-US/Installation.xml Diff File

Issue History

Date Modified Username Field Change
2013-11-07 05:04 kensonman New Issue
2013-11-07 05:06 kensonman Note Added: 0038455
2013-11-08 08:25 dregad Note Added: 0038511
2013-11-08 08:25 dregad Status new => acknowledged
2013-11-08 08:36 dregad Relationship added related to 0007849
2013-11-11 01:39 kensonman Note Added: 0038526
2013-11-11 03:14 dregad Note Added: 0038527
2013-11-11 03:14 dregad Note Edited: 0038527 View Revisions
2014-02-04 10:40 dregad Changeset attached => MantisBT master d46001bd
2014-02-04 10:40 dregad Assigned To => dregad
2014-02-04 10:40 dregad Status acknowledged => resolved
2014-02-04 10:40 dregad Resolution open => fixed
2014-02-04 10:40 dregad Fixed in Version => 1.3.0-beta.1
2014-02-04 11:23 dregad Target Version => 1.3.0-beta.1
2014-12-08 00:34 vboctor Status resolved => closed