View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0006296 | mantisbt | filters | public | 2005-09-27 10:41 | 2006-02-04 05:54 |
Reporter | rscott | Assigned To | thraxisp | ||
Priority | normal | Severity | major | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Product Version | 1.0.0rc2 | ||||
Fixed in Version | 1.0.0rc3 | ||||
Summary | 0006296: Filter sql includes unnecessary links to custom_field_string_table for date custom fields | ||||
Description | Most noticeable when using the 'View Issues' link. Example SQL produced:- SELECT DISTINCT mantis_bug_table.* FROM mantis_bug_table | ||||
Steps To Reproduce | Add several date format custom fields to a project. Click view all and examine the SQL produced. | ||||
Tags | No tags attached. | ||||
Attached Files | custom_date_filter.patch (3,965 bytes)
--- defect/core/filter_api.php 2005-09-22 09:57:01.000000000 +0100 +++ defect-rc2/core/filter_api.php 2005-09-27 16:00:18.000000000 +0100 @@ -611,17 +611,24 @@ # Ignore all custom filters that are not set, or that are set to '' or "any" $t_any_found = false; foreach( $t_filter['custom_fields'][$t_cfid] as $t_filter_member ) { + if ( ( META_FILTER_ANY == $t_filter_member ) || ( 0 === $t_filter_member ) ) { - $t_any_found = true; - } + + $t_any_found = true; + } + } if ( !isset( $t_filter['custom_fields'][$t_cfid] ) ) { + $t_any_found = true; } + + if ( !$t_any_found ) { + + $t_def = custom_field_get_definition( $t_cfid ); - $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; - array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); + if ($t_def['type'] == CUSTOM_FIELD_TYPE_DATE) { switch ($t_filter['custom_fields'][$t_cfid][0]) { @@ -629,24 +636,35 @@ break ; case CUSTOM_FIELD_DATE_NONE: // need to modify that last join, nasty I know but unless you want to upgrade everyone using mysql to 4.1..... + $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; + array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); + $t_my_join = array_pop($t_join_clauses) ; $t_my_join .= ' AND ' . $t_table_name . '.field_id = ' . $t_cfid ; array_push( $t_join_clauses, $t_my_join ) ; $t_custom_where_clause = '(( ' . $t_table_name . '.bug_id is null) OR ( ' . $t_table_name . '.value = 0)' ; break ; case CUSTOM_FIELD_DATE_BEFORE: + $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; + array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); $t_custom_where_clause = '(( ' . $t_table_name . '.field_id = ' . $t_cfid . ' AND ' . $t_table_name . '.value != 0 AND (' . $t_table_name . '.value+0) < ' . ($t_filter['custom_fields'][$t_cfid][2]) . ')' ; break ; case CUSTOM_FIELD_DATE_AFTER: + $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; + array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); $t_custom_where_clause = '(( ' . $t_table_name . '.field_id = ' . $t_cfid . ' AND (' . $t_table_name . '.value+0) > ' . ($t_filter['custom_fields'][$t_cfid][1]+1) . ')' ; break ; default: + $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; + array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); $t_custom_where_clause = '(( ' . $t_table_name . '.field_id = ' . $t_cfid . ' AND (' . $t_table_name . '.value+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2] . ')' ; break ; } } else { foreach( $t_filter['custom_fields'][$t_cfid] as $t_filter_member ) { + $t_table_name = $t_custom_field_string_table . '_' . $t_cfid; + array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" ); if ( isset( $t_filter_member ) && ( META_FILTER_ANY != strtolower( $t_filter_member ) ) ) { @@ -739,6 +757,7 @@ $t_from $t_id_join $t_id_where"; + if ( ( $i == 0 ) || ( !is_blank( $t_textsearch_wherejoin_clause ) ) ) { $result = db_query( $query ); $row_count = db_num_rows( $result ); @@ -748,6 +767,7 @@ $t_id_array[] = db_prepare_int ( $row['id'] ); } } + } $t_id_array = array_unique( $t_id_array ); | ||||
Steps to Reproduce slightly incorrect - should be:- Add several date format custom fields to a project. Click 'View Issues', set all filter values to '[any]' or '[none]' and Apply. |
|
Problem possibly within filter_api.php, lines following comment ' # Ignore all custom filters that are not set, or that are set to '' or "any"' .. Fairly messy patch attached to resolve this - moves the code responsible for adding the left join to inside each case statement. This should hopefully mean that the left join is only applied if an actual date filter is required. |
|
fixed in CVS on 1.0.0rc3 branch and HEAD core/filter_api.php -> 1.122.2.2 or 1.125 |
|