View Issue Details

IDProjectCategoryView StatusLast Update
0011279mantisbtdb postgresqlpublic2016-06-12 00:42
ReportergthomasAssigned Todregad 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version1.2.0rc2 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0011279: 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)

Tagspatch

Relationships

has duplicate 0011963 closeddregad error filtering with a custom field of type Date 

Activities

gthomas

gthomas

2009-12-09 09:50

reporter  

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 {
gthomas

gthomas

2009-12-30 07:54

reporter  

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 {
gthomas

gthomas

2009-12-30 07:55

reporter   ~0024014

Added a patch for trunk (58e01600767cf8114a8396595920222d01f424d7).

GThomas

gthomas

gthomas

2010-09-11 02:12

reporter  

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

gthomas

gthomas

2010-09-11 02:14

reporter   ~0026674

Added new patch, correcting NULL values ( + COALESCE + NULLIF ).
Format is as required (git format-patch --binary)

dregad

dregad

2011-07-22 13:12

developer   ~0029274

The proposed patch would cause the system to break other RDBMS where NULLIF function or FLOAT type do not exist.

grangeway

grangeway

2014-05-16 15:02

reporter   ~0040500

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

atrol

atrol

2014-06-16 15:36

developer   ~0040813

Reminder sent to: dregad, grangeway

Not sure, maybe this issue should be moved back to project mantis.
If so, grangeway could make a copy of it in project "Track DBAL replacement"

dregad

dregad

2016-01-08 08:55

developer   ~0052281

@gthomas, does this still occur with latest master ?

gthomas

gthomas

2016-01-12 07:18

reporter   ~0052297

No, it is fixed in 1.3-rc2 (checked the generated query, too).

dregad

dregad

2016-01-12 08:16

developer   ~0052298

Thanks for your feedback !

Issue History

Date Modified Username Field Change
2009-12-09 09:50 gthomas New Issue
2009-12-09 09:50 gthomas File Added: custom_date_field_filter.patch
2009-12-11 10:39 vboctor Tag Attached: patch
2009-12-11 10:40 vboctor Status new => acknowledged
2009-12-11 10:40 vboctor Category filters => db oracle
2009-12-30 07:54 gthomas File Added: custom_date_field_filter-1.3.0dev.patch
2009-12-30 07:55 gthomas Note Added: 0024014
2010-09-11 02:12 gthomas File Added: custom_date_field_filter-git-922cc05.patch
2010-09-11 02:14 gthomas Note Added: 0026674
2010-10-22 10:02 dhx Target Version => 1.2.4
2010-12-14 21:05 jreese Target Version 1.2.4 => 1.2.5
2011-04-05 12:25 jreese Target Version 1.2.5 => 1.2.6
2011-05-27 08:52 rombert Category db oracle => db postgresql
2011-07-22 13:09 dregad Relationship added has duplicate 0011963
2011-07-22 13:12 dregad Note Added: 0029274
2011-07-26 09:53 jreese Target Version 1.2.6 => 1.2.7
2011-08-22 10:49 jreese Target Version 1.2.7 => 1.2.8
2011-09-06 10:33 jreese Target Version 1.2.8 => 1.2.9
2012-03-04 09:23 atrol Target Version 1.2.9 => 1.2.10
2012-04-02 02:33 atrol Target Version 1.2.10 => 1.2.11
2012-06-06 23:54 jreese Target Version 1.2.11 => 1.2.12
2012-11-10 19:04 dregad Target Version 1.2.12 => 1.2.13
2013-01-22 09:48 dregad Target Version 1.2.13 => 1.2.14
2013-01-29 09:28 dregad Target Version 1.2.14 => 1.2.15
2013-04-12 09:57 dregad Target Version 1.2.15 => 1.2.16
2014-01-23 17:51 atrol Target Version 1.2.16 =>
2014-04-12 19:38 grangeway Target Version => 1.3.0-beta.1
2014-04-12 19:57 grangeway Status acknowledged => assigned
2014-04-12 19:57 grangeway Assigned To => grangeway
2014-05-16 15:02 grangeway Note Added: 0040500
2014-05-23 15:05 grangeway Project mantisbt => @24@
2014-06-16 15:36 atrol Note Added: 0040813
2014-11-07 14:26 atrol Project @24@ => mantisbt
2014-11-07 16:46 atrol Assigned To grangeway =>
2014-11-07 16:46 atrol Status assigned => acknowledged
2014-12-08 02:10 atrol Target Version 1.3.0-beta.1 => 1.3.0-beta.2
2015-03-15 20:00 dregad Target Version 1.3.0-beta.2 => 1.3.0-beta.3
2015-09-06 17:47 vboctoradmin Target Version 1.3.0-beta.3 => 1.3.0-rc.1
2016-01-08 08:55 dregad Note Added: 0052281
2016-01-12 03:43 atrol Target Version 1.3.0-rc.1 => 1.3.0-rc.2
2016-01-12 07:18 gthomas Note Added: 0052297
2016-01-12 08:16 dregad Assigned To => dregad
2016-01-12 08:16 dregad Status acknowledged => resolved
2016-01-12 08:16 dregad Resolution open => fixed
2016-01-12 08:16 dregad Fixed in Version => 1.3.0-rc.2
2016-01-12 08:16 dregad Note Added: 0052298
2016-06-12 00:42 vboctor Status resolved => closed