View Issue Details

IDProjectCategoryView StatusLast Update
0012787mantisbtfilterspublic2018-10-31 16:39
Reportermeeuw Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status confirmedResolutionopen 
Product Version1.2.4 
Target VersionFixed in Version 
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

Relationships

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

Activities

meeuw

meeuw

2011-02-23 04:43

reporter  

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

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 <pre>IN (SELECT foo FROM bar)</pre> 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 <pre>
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'
)
</pre> is equivalent to (order by clause to maintain same order, may not be necessary)<pre>
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
</pre>

Issue History

Date Modified Username Field Change
2011-02-18 10:24 meeuw New Issue
2011-02-23 04:43 meeuw File Added: 0001-fix-for-mantisbt.org-issue-12787-negate-text-search-.patch
2011-02-25 21:14 dhx Status new => confirmed
2011-02-25 21:15 dhx Target Version => 1.2.5
2011-02-25 21:15 dhx Tag Attached: patch
2011-04-05 12:25 jreese Target Version 1.2.5 => 1.2.6
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-02 07:59 dhx Note Added: 0031982
2012-06-03 12:06 dregad Note Added: 0031992
2012-06-04 07:49 meeuw Note Added: 0031999
2012-06-04 08:32 dregad Note Added: 0032000
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:55 atrol Target Version 1.2.16 =>
2018-10-31 16:39 atrol Relationship added related to 0024922