286461 - Unknown table 'map_products' in where clause

Status: RESOLVED FIXED

(Bugzilla :: Reporting/Charting, defect)

Description

[Kristoffer Lidström]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; rv:1.7.3) Gecko/20041020 Firefox/0.10.1
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; rv:1.7.3) Gecko/20041020 Firefox/0.10.1

When creating a graphical report and selecting bugs using the Classification
filter but not using Classification in any of the axis the query fails:

DBD::mysql::st execute failed: Unknown table 'map_products' in where clause [for
Statement "SELECT 42217354, map_components.name, 42217354 FROM bugs, components
AS map_components  LEFT JOIN bug_group_map  ON bug_group_map.bug_id =
bugs.bug_id  AND bug_group_map.group_id NOT IN
(7,3,10,5,14,1,8,11,12,4,2,6,13,15,9)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id
AND cc.who = 1 WHERE bugs.component_id = map_components.id AND
((map_products.classification_id IN (3)) AND (bugs.bug_status IN
('NEW','ASSIGNED','REOPENED'))) AND ((bug_group_map.group_id IS NULL)    OR
(bugs.reporter_accessible = 1 AND bugs.reporter = 1)     OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to =
1) OR (bugs.qa_contact = 1) ) GROUP BY bugs.bug_id"] at Bugzilla/DB.pm line 62
	Bugzilla::DB::SendSQL('SELECT 42217354, map_components.name, 42217354 FROM
bugs, com...') called at /var/www/html/Testzilla/report.cgi line 155

Reproducible: Always

Steps to Reproduce:
1.Create a classification
2.Add a product to classification
3.Add a few components to product
4.Add a few bugs to components
5.Go to Generate graphical report
6.Select a classification in the classification drop down list
7.select Component on horizontal axis

Breaks

It works if Component is selected on any of the axis.



I edited report.cgi and changed line 140 from:

if (lsearch(\@axis_fields,"classification") >= 0) {

to

if (lsearch(\@axis_fields,"classification") >= 0 || defined
$cgi->param('classification')) {

which seems to solve the problem.
This is my first time messing with bugzilla code, does my change make sense?

Comment 1

[Frédéric Buclin]

I can reproduce

Comment 2

[Gervase Markham [:gerv]]

This is a problem in Search.pm caused by the implementation of classifications.
On line 138, it references a table alias name which has not necessarily been
defined. Reassigning the bug to the classifications implementor.

Gerv

Comment 3

[Albert Ting]

Attachment: first cut

I'm not confident if this is the right approach, but I believe this patch will
fix the problem. Can you give it a shot?

Comment 4

[Gervase Markham [:gerv]]

Sorry, mate, that doesn't work. The following localhost URL gives the error the
bug is about:
http://localhost/bugzilla/report.cgi?x_axis_field=op_sys&y_axis_field=rep_platform&z_axis_field=&query_format=report-table&short_desc_type=allwordssubstr&short_desc=&classification=New&long_desc_type=substring&long_desc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&keywords_type=allwords&keywords=&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&emailassigned_to1=1&emailtype1=substring&email1=&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailcc2=1&emailtype2=substring&email2=&bugidtype=include&bug_id=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&format=table&action=wrap&field0-0-0=noop&type0-0-0=noop&value0-0-0=
After applying your patch, I get:

SQL query generator internal error

There is an internal error in the SQL query generation code, creating queries
with implicit JOIN.

Gerv

Comment 5

[Albert Ting]

Darn.  This works with my 2.19.2-based production system.  I see that Search.pm
has changed a bit since then.  Will have to upgrade to the tip before trying again. 

Comment 6

[Albert Ting]

Attachment: second cut

Gerv, give this one a shot.  I've updated my tip and this works for me.  But
I'm not familiar with Search.pm and am not confident I can just add the
additional sql commands to the @supptables array.  Curious if there might be a
conflict if it's also added here:

   if (lsearch($fieldsref, 'map_products.name') >= 0) {
	push @supptables, "INNER JOIN products AS map_products " .
			  "ON bugs.product_id = map_products.id";
   }

Comment 7

[Gervase Markham [:gerv]]

Comment on attachment 179081 [details] [diff] [review]
second cut

This doesn't help at all, I'm afraid. I still get the same error.

You may want to summon some of the Bugzilla SQL gurus to help you, and/or make
sure you can reproduce the problem and that it goes away with your next
attempt. :-)

Gerv

Comment 8

[Joel Peshkin]

Attachment: Fixed the same way as buglist does it

The problem is that product was not added unless is was an axis.  Buglist adds
product regardless so long as classifications are in use.  Probably, Search.pm
should contain this logic as well as any other similar prerequisite decisions.
But, that is another bug

Comment 9

[Joel Peshkin]

Comment on attachment 179384 [details] [diff] [review]
Fixed the same way as buglist does it

Actually, on second thought, this wants to be fixed in Search.pm regardless. 
Fixing it in every caller is silly.

Comment 10

[Joel Peshkin]

Attachment: Fix it right

Comment 11

[Albert Ting]

Hi Gerv, I was able to see the failure and confirmed the original patch did fix
the problem for me.  But acknowledge my setup uses various other
patches/customization which may be conflicting with my solution.

In any case, thanks Joel for providing the right solution.

Comment 12

[Gervase Markham [:gerv]]

Comment on attachment 179394 [details] [diff] [review]
Fix it right

Seems to work for me. r=gerv.

Gerv

Comment 13

[Joel Peshkin]

Checking in report.cgi;
/cvsroot/mozilla/webtools/bugzilla/report.cgi,v  <--  report.cgi
new revision: 1.29; previous revision: 1.28
done
Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.93; previous revision: 1.92
done