View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0012787mantisbtfilterspublic2011-02-18 10:242018-10-31 16:39
Reportermeeuw Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status confirmedResolutionopen 
Product Version1.2.4 
Summary0012787: negate text search doesn't work for multiple bug notes
Description

Negate text search doesn't work for comments, when a bug has multiple bug notes and one of these notes does contain the search string and another doesn't, it should be matched. All comments should be evaluated as one.

Steps To Reproduce
  1. Report an issue, fill in all required values
  2. Add bugnote "abcdefg"
  3. Add bugnote "hijklmn"
  4. Search for -abcdefg
Additional Information

Current behavior:

  • The reported issue is matched because it has a bug note "hijklm"

Expected behavior:

  • The reported issue shouldn't be matched
Tagspatch
Attached Files
0001-fix-for-mantisbt.org-issue-12787-negate-text-search-.patch (1,772 bytes)    
From 35a87b7ecce6ff6be3ca9f94ec1aa453347950b5 Mon Sep 17 00:00:00 2001
From: meeuw <dick@mrns.nl>
Date: Wed, 23 Feb 2011 10:37:45 +0100
Subject: [PATCH] fix for mantisbt.org issue 12787 - negate text search doesn't work for multiple bug notes

---
 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 0c6d5b7..6604712 100644
--- a/core/filter_api.php
+++ b/core/filter_api.php
@@ -1973,7 +1973,10 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
 				' OR ' . db_helper_like( "$t_bug_text_table.description" ) .
 				' OR ' . db_helper_like( "$t_bug_text_table.steps_to_reproduce" ) .
 				' OR ' . db_helper_like( "$t_bug_text_table.additional_information" ) .
-				' OR ' . db_helper_like( "$t_bugnote_text_table.note" );
+				' OR ' . "$t_bug_table.id" . " IN (".
+					"SELECT bug_id FROM $t_bugnote_table".
+					" LEFT JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id".
+					" WHERE ($t_bugnote_text_table.note LIKE ".db_param()."))";
 
 			$t_where_params[] = $c_search;
 			$t_where_params[] = $c_search;
@@ -1997,10 +2000,7 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
 		# add text query elements to arrays
 		if ( !$t_first ) {
 			$t_from_clauses[] = "$t_bug_text_table";
-			$t_where_clauses[] = "$t_bug_table.bug_text_id = $t_bug_text_table.id";
 			$t_where_clauses[] = $t_textsearch_where_clause;
-			$t_join_clauses[] = " LEFT JOIN $t_bugnote_table ON $t_bug_table.id = $t_bugnote_table.bug_id";
-			$t_join_clauses[] = " LEFT JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id";
 		}
 	}
 
-- 
1.7.3.4
0001-fix-for-mantisbt.org-issue-12787-negate-text-search-.patch (1,772 bytes)   

Relationships

Relationship GraphDependency Graph
related to 0024922 new Text field search with negation does not returns issues without bugnotes 

Activities

dhx

dhx

2012-06-02 07:59

reporter   ~0031982

WHERE ($t_bugnote_text_table.note LIKE ".db_param()."))";

This should use db_helper_like to handle case insensitivity/etc?

The patch looks OK but I'd like an indication of what effect this patch has on search performance.
dregad

dregad

2012-06-03 12:06

developer   ~0031992

In my opinion, constructs like 

IN (SELECT foo FROM bar)
should be avoided; it's generally better from a performance perspective to use an inner join, and would also avoid RDBMS limitations on how many entries are allowed in the list.
meeuw

meeuw

2012-06-04 07:49

reporter   ~0031999

I didn't notice any bad performance, but our mantis database was small (2k issues).

Do you have a test data set so I can measure the performance impact?

If Dregad is right we should use an inner join, but when I wrote this patch I couldn't write it as a inner join.
dregad

dregad

2012-06-04 08:32

developer   ~0032000

Without going into the details of the actual query in the filter_api, on principle 



select b.id

from mantis_bug_table b

where b.id in (

select bug_id

from mantis_bugnote_table n

left join mantis_bugnote_text_table t on n.bugnote_text_id = t.id

where t.note like 'x'

)
is equivalent to (order by clause to maintain same order, may not be necessary)


select b.id

from mantis_bug_table b

inner join (

select distinct bug_id

from mantis_bugnote_table

left join mantis_bugnote_text_table t on bugnote_text_id = t.id

where t.note like 'x') n on b.id = n.bug_id

order by 1