View Issue Details

IDProjectCategoryView StatusLast Update
0007809mantisbtcustom fieldspublic2007-10-04 01:37
Reporterlifo2 
Assigned Togiallu 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version 
Target VersionFixed in Version1.1.0rc1 
Summary0007809: Filter "none" on custom fields doesn't work
Description

When filtering on a custom field and choosing the value [none], the result is empty.

The reason is that theres no entry in the mantis_custom_field_string_table for empty lines (except if the field was first filled and then updated to empty).

Steps To Reproduce

Pre-requisites :
1 - Create a custom field as administrator in [Manage custom fields]
2 - Add this custom field to a project in [Manage project]
3 - Create an anomaly in this project and let the custom field empty

Reproducing :
1 - In view issues, select [none] as the value to filter on for this field
2 - Click on Apply Filter
3 - See the empty result

Additional Information

This could be solved by a special query for the [none] value like :

SELECT ... FROM mantis_bug_table WHERE bug_id NOT IN (SELECT DISTINCT(bug_id) FROM mantis_custom_field_string_table WHERE field_id=...)

TagsNo tags attached.

Relationships

Activities

2007-03-09 04:47

 

custom_field_filter_none.patch (1,129 bytes)
Index: core/filter_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v
retrieving revision 1.147
diff -u -r1.147 filter_api.php
--- core/filter_api.php	19 Dec 2006 09:15:54 -0000	1.147
+++ core/filter_api.php	9 Mar 2007 10:46:21 -0000
@@ -784,8 +784,17 @@
 						array_push( $t_join_clauses, $t_cf_join_clause );
 						foreach( $t_filter['custom_fields'][$t_cfid] as $t_filter_member ) {
 							$t_filter_member = stripslashes( $t_filter_member );
-							if ( META_FILTER_NONE === $t_filter_member ) { # coerce filter value if selecting META_FILTER_NONE
+							if ( META_FILTER_NONE == $t_filter_member ) { # coerce filter value if selecting META_FILTER_NONE
 								$t_filter_member = '';
+
+								if( $t_first_time ) {
+									$t_first_time = false;
+									$t_custom_where_clause = '(';
+								} else {
+									$t_custom_where_clause .= ' OR ';
+								}
+									
+								$t_custom_where_clause .= "$t_bug_table.id NOT IN (SELECT bug_id FROM $t_table_name WHERE field_id=$t_cfid)";
 							}
 
 							if( $t_first_time ) {
lifo2

lifo2

2007-03-09 04:50

reporter   ~0014148

Here is a patch that correct this problem.

In fact, there were two problems :

  • no line in the table for bug added before the custom field was added in the project (solved thanks to the SELECT ... WHERE id NOT IN (...))

  • no result at all because of a wrong comparison between META_FILTER_NONE and $t_filter_member

2007-03-09 05:25

 

custom_field_filter_none_2.patch (1,144 bytes)
Index: core/filter_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v
retrieving revision 1.147
diff -u -r1.147 filter_api.php
--- core/filter_api.php	19 Dec 2006 09:15:54 -0000	1.147
+++ core/filter_api.php	9 Mar 2007 11:23:37 -0000
@@ -784,8 +784,17 @@
 						array_push( $t_join_clauses, $t_cf_join_clause );
 						foreach( $t_filter['custom_fields'][$t_cfid] as $t_filter_member ) {
 							$t_filter_member = stripslashes( $t_filter_member );
-							if ( META_FILTER_NONE === $t_filter_member ) { # coerce filter value if selecting META_FILTER_NONE
+							if ( META_FILTER_NONE == $t_filter_member ) { # coerce filter value if selecting META_FILTER_NONE
 								$t_filter_member = '';
+
+								if( $t_first_time ) {
+									$t_first_time = false;
+									$t_custom_where_clause = '(';
+								} else {
+									$t_custom_where_clause .= ' OR ';
+								}
+									
+								$t_custom_where_clause .= "$t_bug_table.id NOT IN (SELECT bug_id FROM $t_custom_field_string_table WHERE field_id=$t_cfid)";
 							}
 
 							if( $t_first_time ) {
lifo2

lifo2

2007-03-09 05:26

reporter   ~0014149

Sorry, I made a mistake in the first patch. The second one is the good one.

giallu

giallu

2007-07-16 18:42

reporter   ~0015070

I finally had a chance at reviewing your patch.
You are definitely right on the diagnosis and the proposed fix works, but I reworked the patch a little becasue I did not like too much the $t_first_time usage (I know, its not your faukt since you copy/paste it...) Im attaching the reworked patch here, please test it and let me know if it also works for you.

One question; the query you proposed in Additional Information had a DISTINCT clause: is there a reason why you dropped it in the proposed patch?

2007-07-16 18:43

 

bug7809.patch (2,563 bytes)
Index: core/filter_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v
retrieving revision 1.155
diff -u -r1.155 filter_api.php
--- core/filter_api.php	27 May 2007 22:31:59 -0000	1.155
+++ core/filter_api.php	16 Jul 2007 22:42:57 -0000
@@ -1067,7 +1067,6 @@
 			$t_custom_fields = custom_field_get_linked_ids( $t_project_id );
 
 			foreach( $t_custom_fields as $t_cfid ) {
-				$t_first_time = true;
 				$t_custom_where_clause = '';
 				# Ignore all custom filters that are not set, or that are set to '' or "any"
 				$t_any_found = false;
@@ -1112,27 +1111,27 @@
 					} else {
 
 						array_push( $t_join_clauses, $t_cf_join_clause );
+
+						$t_filter_array = array();
 						foreach( $t_filter['custom_fields'][$t_cfid] as $t_filter_member ) {
 							$t_filter_member = stripslashes( $t_filter_member );
-							if ( META_FILTER_NONE === $t_filter_member ) { # coerce filter value if selecting META_FILTER_NONE
+							if ( META_FILTER_NONE == $t_filter_member ) { 
+								# coerce filter value if selecting META_FILTER_NONE so it will match empty fields
 								$t_filter_member = '';
+								# but also add those _not_ present in the custom field string table
+								array_push( $t_filter_array , "$t_bug_table.id NOT IN (SELECT bug_id FROM $t_custom_field_string_table WHERE field_id=$t_cfid)" );
 							}
 
-							if( $t_first_time ) {
-								$t_first_time = false;
-								$t_custom_where_clause = '(';
-							} else {
-								$t_custom_where_clause .= ' OR ';
-							}
 							switch( $t_def['type'] ) {
-							case CUSTOM_FIELD_TYPE_MULTILIST:
-							case CUSTOM_FIELD_TYPE_CHECKBOX:
-								$t_custom_where_clause .= db_helper_like( "$t_table_name.value", '%|' . db_prepare_string( $t_filter_member ) . '|%' );
-								break;
-							default:
-								$t_custom_where_clause .= "$t_table_name.value = '" . db_prepare_string( $t_filter_member ) . "'";
+								case CUSTOM_FIELD_TYPE_MULTILIST:
+								case CUSTOM_FIELD_TYPE_CHECKBOX:
+									array_push( $t_filter_array , db_helper_like( "$t_table_name.value", '%|' . db_prepare_string( $t_filter_member ) . '|%' ) );
+									break;
+								default:
+									array_push( $t_filter_array, "$t_table_name.value = '" . db_prepare_string( $t_filter_member ) . "'" );
 							}
 						}
+						$t_custom_where_clause .= '(' . implode( ' OR ', $t_filter_array );
 					}
 					if ( !is_blank( $t_custom_where_clause ) ) {
 						array_push( $t_where_clauses, $t_custom_where_clause . ')' );
bug7809.patch (2,563 bytes)
lifo2

lifo2

2007-09-13 10:05

reporter   ~0015626

It seems fine to me (and yes, the use of an array and implode is more elegant ;) ).

I think I dropped the DISTINCT clause because there can be only one row for a given couple of bug_id and field_id, so it was useless.

giallu

giallu

2007-09-13 13:24

reporter   ~0015629

In the meanwhile, I become aware thart the nested query syntax could be problematic for the cross DB goal of mantis (and even in MySQL was introduced in v4.1, while we are going to require 4.0)

Do you think that doing 2 separate queries would be detrimental to performances and/or RAM usage?

jreese

jreese

2007-09-13 15:41

reporter   ~0015632

@giallu: I thought we were going to be requiring MySQL 4.1 specifically for the reason of subquery support?

giallu

giallu

2007-09-14 02:23

reporter   ~0015635

I will ask again on -devel

exk72

exk72

2007-09-14 20:14

reporter   ~0015639

Shouldnt / doesnt ADODB handle subquery support?

giallu

giallu

2007-09-15 03:55

reporter   ~0015640

I wish I knew...

anyway, Victor gave me the OK to use subqueries, so 1.1 will have a dependency on MySQL 4.1 and I can commit the fix as is.

Related Changesets

MantisBT: master bd7cad4f

2007-09-15 21:48:26

giallu

Details Diff
Fix 7809: Filter "none" on custom fields doesn't work

git-svn-id: http://mantisbt.svn.sourceforge.net/svnroot/mantisbt/trunk@4574 <a class="text" href="/?p=mantisbt.git;a=object;h=f5dc347c">f5dc347c</a>-c33d-0410-90a0-b07cc1902cb9
mod - core/filter_api.php Diff File

Issue History

Date Modified Username Field Change
2007-03-09 03:08 lifo2 New Issue
2007-03-09 04:47 lifo2 File Added: custom_field_filter_none.patch
2007-03-09 04:50 lifo2 Note Added: 0014148
2007-03-09 05:25 lifo2 File Added: custom_field_filter_none_2.patch
2007-03-09 05:26 lifo2 Note Added: 0014149
2007-07-02 11:41 giallu Status new => assigned
2007-07-02 11:41 giallu Assigned To => giallu
2007-07-16 18:42 giallu Note Added: 0015070
2007-07-16 18:43 giallu File Added: bug7809.patch
2007-09-13 10:05 lifo2 Note Added: 0015626
2007-09-13 13:24 giallu Note Added: 0015629
2007-09-13 15:41 jreese Note Added: 0015632
2007-09-14 02:23 giallu Note Added: 0015635
2007-09-14 20:14 exk72 Note Added: 0015639
2007-09-15 03:55 giallu Note Added: 0015640
2007-09-15 17:54 giallu Status assigned => resolved
2007-09-15 17:54 giallu Fixed in Version => 1.1.0rc1
2007-09-15 17:54 giallu Resolution open => fixed
2007-10-04 01:37 vboctor Status resolved => closed
2008-10-20 20:21 Changeset attached master-1.1.x 148b3cce =>
2008-11-11 08:35 giallu Changeset attached master bd7cad4f =>
2008-11-11 08:48 giallu Changeset attached master bd7cad4f =>