View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0011279 | mantisbt | db postgresql | public | 2009-12-09 09:50 | 2016-06-12 00:42 |
Reporter | gthomas | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Product Version | 1.2.0rc2 | ||||
Target Version | 1.3.0-rc.2 | Fixed in Version | 1.3.0-rc.2 | ||
Summary | 0011279: With PostgreSQL filtering for a date custom field yields to an error | ||||
Description | PostgreSQL backend, date type custom field; filtering for this custom field yields to a DB error (mantis_custom_field_string_table.value+0 is not acceptable for STRING typed fields). | ||||
Additional Information | Attached a patch (CAST TO FLOAT) | ||||
Tags | patch | ||||
Attached Files | custom_date_field_filter.patch (1,481 bytes)
diff -r f71444f6c1b7 core/filter_api.php --- a/core/filter_api.php Mon Dec 07 11:14:26 2009 +0100 +++ b/core/filter_api.php Wed Dec 09 15:44:44 2009 +0100 @@ -1844,15 +1844,15 @@ 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(' . $t_table_name . '.value AS FLOAT)+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 ); + $t_custom_where_clause = '( (CAST(' . $t_table_name . '.value AS FLOAT)+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]; + $t_custom_where_clause = '( (CAST(' . $t_table_name . '.value AS FLOAT)+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2]; break; } } else { custom_date_field_filter-1.3.0dev.patch (1,921 bytes)
diff --git a/core/filter_api.php b/core/filter_api.php index 37783ae..8cea3df 100644 --- a/core/filter_api.php +++ b/core/filter_api.php @@ -1865,19 +1865,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(' . $t_table_name . '.value AS FLOAT) = 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 = '(( CAST(' . $t_table_name . '.value AS FLOAT) != 0 AND ( CAST(' . $t_table_name . '.value AS FLOAT) + 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 ); + $t_custom_where_clause = '( (CAST(' . $t_table_name . '.value AS FLOAT) + 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]; + $t_custom_where_clause = '( ( CAST(' . $t_table_name . '.value AS FLOAT) + 0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2]; break; } } else { custom_date_field_filter-git-922cc05.patch (2,454 bytes)
From ad413a2af0c11478d15bc39a5ff161cc69e3719a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Tam=C3=A1s=20Gul=C3=A1csi?= <gthomas@hackworth.guba> Date: Sat, 11 Sep 2010 07:57:53 +0200 Subject: [PATCH] fix custom_date filter CAST/COALESCE/NULLIF error --- core/filter_api.php | 8 +++--- 1 files changed, 4 insertions(+), 4 deletions(-) diff --git a/core/filter_api.php b/core/filter_api.php index 922cc05..a1da0cc 100644 --- a/core/filter_api.php +++ b/core/filter_api.php @@ -1889,19 +1889,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.0') AS FLOAT) = 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 = '(( CAST( COALESCE( NULLIF(' . $t_table_name . ".value, ''), '0.0') AS FLOAT) != 0 AND ( CAST( COALESCE( NULLIF(" . $t_table_name . ".value, ''), '0.0') AS FLOAT) + 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 ); + $t_custom_where_clause = '( (CAST( COALESCE( NULLIF(' . $t_table_name . ".value, ''), '0.0') AS FLOAT) + 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]; + $t_custom_where_clause = '( ( CAST( COALESCE( NULLIF(' . $t_table_name . ".value, ''), '0.0') AS FLOAT) + 0) BETWEEN " . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2]; break; } } else { -- 1.7.1 | ||||
Added a patch for trunk (58e01600767cf8114a8396595920222d01f424d7). GThomas |
|
Added new patch, correcting NULL values ( + COALESCE + NULLIF ). |
|
The proposed patch would cause the system to break other RDBMS where NULLIF function or FLOAT type do not exist. |
|
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 |
|
Reminder sent to: dregad, grangeway Not sure, maybe this issue should be moved back to project mantis. |
|
@gthomas, does this still occur with latest master ? |
|
No, it is fixed in 1.3-rc2 (checked the generated query, too). |
|
Thanks for your feedback ! |
|