0005977: SQL error when setting filter - MantisBT

ID	Project	Category	View Status	Date Submitted	Last Update
0005977	mantisbt	custom fields	public	2005-07-19 09:23	2014-01-29 15:50

Reporter	hgaland	Assigned To	atrol
Priority	normal	Severity	crash	Reproducibility	always
Status	closed	Resolution	no change required
Product Version	1.0.0a3

Summary	0005977: SQL error when setting filter
Description	Using custom fields intensively, I found several problem with filter display and resulting SQL: All are linked and one is crashing First case: ANNOYING 1) goto the view issues page filtering on one of the projects 2) change the filter to see all status (do not hide closed) => In the first attempt the filter is not set correctly (still hide closed) => Secon attempt, filter is set and SQL request works fine Second case : FILTER CAN'T BE SET The conditions are the following 1) Have a Date custom field 2) Link this custom field to more than one project 3) goto the view issues page filtering all projects => The custom fields appear several times which is not correct if one of the custom_field_sequence is different of "0" => Even if it appears more than once, the update link drive to one location (the first one) => The consequence is that a filter on this field cannot be set because it is reset to the value of the second occurence (any) Third case : CRASH The conditions are the following 1) Have a Date custom field (this is important to be able to test because of bug in second case) 2) Link this custom field to more than one project (with custom_field_sequence <> 0) 3) goto the view issues page filtering all projects 4) change the filter to see all status (do not hide closed) (twice is the first case is not solved) => The SQL abort because of incorrect clause => In fact, as the custom fields filters appear more than once, the mantis_custom_field_string_table appears several time with the same alias in the SQL query you get something like that if have a custom field (id=3) used in 3 projects SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_3 <=== ON mantis_custom_field_string_table_3.bug_id = mantis_bug_table.id LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_3 <=== ON mantis_custom_field_string_table_3.bug_id = mantis_bug_table.id LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_3 <=== ON mantis_custom_field_string_table_3.bug_id = mantis_bug_table.id WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.status in (5, 10, 20, 25, 30, 35, 36, 40, 50, 60, 65, 70, 75, 77, 80) ) AND (mantis_bug_text_table.id = mantis_bug_table.bug_text_id) AND ((summary LIKE '%test%') OR (mantis_bug_text_table.description LIKE '%test%') OR (mantis_bug_text_table.steps_to_reproduce LIKE '%test%') OR (mantis_bug_text_table.additional_information LIKE '%test%') OR (mantis_bug_table.id = '0'));
Tags	No tags attached.
Attached Files	patch.txt (4,077 bytes) --- \\lssapiw01\c$\Inetpub\MantisTest\\..\MantisStandard\return_dynamic_filters.php 2005-06-04 17:39:28.000000000 +0200 +++ \\lssapiw01\c$\Inetpub\MantisTest\return_dynamic_filters.php 2005-07-19 17:02:51.988529800 +0200 @@ -40,7 +40,12 @@ if ( ON == config_get( 'filter_by_custom_fields' ) ) { $t_custom_cols = config_get( 'filter_custom_fields_per_row' ); + /* PATCH Custom fields should not appear more than one in filter / + / $t_custom_fields = custom_field_get_linked_ids( $t_project_id ); + / + $t_custom_fields = custom_field_get_all_unique_linked_ids( $t_project_id ); + / PATCH Custom fields should not appear more than one in filter / foreach ( $t_custom_fields as $t_cfid ) { $t_field_info = custom_field_cache_row( $t_cfid, true ); --- \\lssapiw01\c$\Inetpub\MantisTest\\..\MantisStandard\view_filters_page.php 2005-06-13 14:05:43.397658600 +0200 +++ \\lssapiw01\c$\Inetpub\MantisTest\view_filters_page.php 2005-07-19 17:02:57.004122700 +0200 @@ -87,7 +87,12 @@ if ( ON == config_get( 'filter_by_custom_fields' ) ) { $t_custom_cols = $t_filter_cols; + / PATCH Custom fields should not appear more than one in filter / + / $t_custom_fields = custom_field_get_linked_ids( $t_project_id ); + / + $t_custom_fields = custom_field_get_all_unique_linked_ids( $t_project_id ); + / PATCH Custom fields should not appear more than one in filter / foreach ( $t_custom_fields as $t_cfid ) { $t_field_info = custom_field_cache_row( $t_cfid, true ); --- \\lssapiw01\c$\Inetpub\MantisStandard\core\custom_field_api.php 2005-07-18 13:48:58.193484400 +0200 +++ \\lssapiw01\c$\Inetpub\MantisTest\core\custom_field_api.php 2005-07-19 17:11:40.016400400 +0200 @@ -664,6 +664,28 @@ return $t_ids; } + / PATCH Unique list of linked custom fields / + # -------------------- + # Return an array of UNIQUE ids of custom fields bound to the specified project and subproject + # + # The ids will be sorted based on the sequence number associated with the binding + function custom_field_get_all_unique_linked_ids( $p_project_id = ALL_PROJECTS ) { + + if ( ALL_PROJECTS == $p_project_id ) { + $t_ids = custom_field_get_linked_ids( $p_project_id ); + } else { + # Build an array of projects + $t_project_ids = user_get_all_accessible_subprojects( auth_get_current_user_id(), $p_project_id ); + array_unshift( $t_project_ids, $p_project_id ); + $t_ids = custom_field_get_linked_ids( $t_project_ids ); + } + + #remove duplicates from array + $t_unique_ids = array_unique($t_ids) ; + + return $t_unique_ids; + } + / PATCH Unique list of linked custom fields / # -------------------- # Return an array all custom field ids sorted by name function custom_field_get_ids( ) { --- \\lssapiw01\c$\Inetpub\MantisStandard\core\filter_api.php 2005-07-19 09:36:29.464776500 +0200 +++ \\lssapiw01\c$\Inetpub\MantisTest\core\filter_api.php 2005-07-19 17:03:56.566241500 +0200 @@ -592,7 +592,12 @@ # custom field filters if( ON == config_get( 'filter_by_custom_fields' ) ) { # custom field filtering + / PATCH Custom fields should not appear more than one in filter / + / $t_custom_fields = custom_field_get_linked_ids( $t_project_id ); + / + $t_custom_fields = custom_field_get_all_unique_linked_ids( $t_project_id ); + / PATCH Custom fields should not appear more than one in filter / foreach( $t_custom_fields as $t_cfid ) { $t_first_time = true; @@ -1008,7 +1013,12 @@ $t_per_row = 0; if ( ON == config_get( 'filter_by_custom_fields' ) ) { + / PATCH Custom fields should not appear more than one in filter / + / $t_custom_fields = custom_field_get_linked_ids( $t_project_id ); + / + $t_custom_fields = custom_field_get_all_unique_linked_ids( $t_project_id ); + / PATCH Custom fields should not appear more than one in filter */ foreach ( $t_custom_fields as $t_cfid ) { $t_field_info = custom_field_cache_row( $t_cfid, true ); patch.txt (4,077 bytes)

thraxisp 2005-07-19 12:43 reporter ~0010870	The key here is that the sequence_id is not 0.

hgaland 2005-07-19 13:23 reporter ~0010871	I found a workaround writing a new function custom_field_get_all_unique_linked_ids This function get the unique list of all custom fields used in the project (and its sub-projects) This function is called in place of custom_field_get_linked_ids in files: return_dynamic_filters.php view_filters_page.php core\filter_api.php According to the patch file attached If this can help you Best regards

thraxisp 2005-07-19 14:34 reporter ~0010875	The first item is a duplicate of 0005786. I'm having some trouble reproducing it. The second and third items are related to multiple entries erroneously showing up when looking for custom fields in "ALL_PROJECTS". This is now fixed in CVS. core/custom_field_api.php -> 1.57

hgaland 2005-07-25 09:52 reporter ~0010943	The problem is not completly fixed in 1.0.0rc1: ALL cases that indeed multiple occurences of custom fields filters are solved. This only case that remains is when you have a project without custom fields but having sub-projects with custom fields: No filters on custom fields of sub-projects appears. It should be logic to see these filters as it is possible to filter bugs of the sub-projects. Further more... It should be nice to have a check box or something like that to specify in the filters if we want to take into account or not sub-projects.

hgaland 2005-07-28 12:54 reporter ~0011006	To help you to reproduce the first item, I noticed that it always appears when you apply a filter for the first time (a fresh new connection). May be a problem with the filter cookie.

grangeway 2008-07-28 14:33 reporter ~0018781	Daryn, can you check the test cases provided are resolved

atrol 2014-01-20 16:51 developer ~0039109	This issue is not reproducible with the current version of MantisBT. I didn't try all cases, but the one which was still not fixed. This only case that remains is when you have a project without custom fields but having sub-projects with custom fields: No filters on custom fields of sub-projects appears. We recommend that you upgrade to the latest stable version [1]; if after doing so the problem persists, do not hesitate to reopen the issue, with additional relevant information. [1] http://www.mantisbt.org/download.php