0004702: Search fails if no bugnote is entered - MantisBT

ID	Project	Category	View Status	Date Submitted	Last Update
0004702	mantisbt	filters	public	2004-10-13 10:39	2006-04-25 09:28

Reporter	gannis	Assigned To	Narcissus
Priority	normal	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed

Summary	0004702: Search fails if no bugnote is entered
Description	The latest (1.59) filter_api.php hides issues that doesn't have any bugnotes yet. A simple search for a word in the summary description gives no hit.
Additional Information	A quick fix where "INNER JOIN" is replaced by "LEFT JOIN" seems to solve the problem. See 0004505 for previous discussion.
Tags	No tags attached.
Attached Files	filter_api.php.diff (802 bytes) --- filter_api.php.1.59 2004-10-05 13:29:11.000000000 +0200 +++ filter_api.php 2004-10-13 17:31:31.000000000 +0200 @@ -469,8 +469,8 @@ $t_from_clauses = array( $t_bug_text_table, $t_project_table, $t_bug_table ); - array_push( $t_join_clauses, "INNER JOIN $t_bugnote_table ON $t_bugnote_table.bug_id = $t_bug_table.id" ); - array_push( $t_join_clauses, "INNER JOIN $t_bugnote_text_table ON $t_bugnote_text_table.id = $t_bugnote_table.bugnote_text_id" ); + array_push( $t_join_clauses, "LEFT JOIN $t_bugnote_table ON $t_bugnote_table.bug_id = $t_bug_table.id" ); + array_push( $t_join_clauses, "LEFT JOIN $t_bugnote_text_table ON $t_bugnote_text_table.id = $t_bugnote_table.bugnote_text_id" ); } else { $t_from_clauses = array( $t_project_table, $t_bug_table ); } filter_api.php.diff (802 bytes)

vboctor 2004-10-16 22:23 manager ~0008080	Reminder sent to Narcissus Narcissus, what do you think?

vboctor 2004-10-17 04:29 manager ~0008081	I have reproduced the problem on my test installation, which has a reasonable amount of issues. When I change INNER JOIN to LEFT JOIN, the View Issues page doesn't show anything, it just keeps working and never displays anything.

thraxisp 2004-10-17 10:27 reporter ~0008083	The switch to LEFT JOIN works for me. I did notice, however, that we make the same complex query twice. First to get the count, then again to fetch data from the bug_table. Each of these queries runs about 4 seconds on my slower host. We might be able to combine them.

Narcissus 2004-10-17 19:14 reporter ~0008086	The LEFT JOIN does work, but is really slow. On one of my test machines it runs in a little under 3 seconds, but on another machine (with a much larger database) it can take up to two minutes. The code that I'm about to submit actually makes a few queries when doing text searching: the first finds issues with matching text in the non-bugnote text areas (a fast search). Then a second query searches the bugnotes using the INNER JOIN (again, a fast search). Finally, I join the unique IDs that are returned and do the final query on this. Although it sounds like a bit of a hack, I don't know any other way to do it quickly and this way is much, much faster than LEFT JOINs. Also, I have removed the query to get the count. Once I submit the code, naturally I'd love some feedback, but (besides the huge, ugly request by IDs) I think it's OK...