289736 - query.cgi - execute failed: Unknown column 'relevance' in 'field list' in 040905 tip

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect, P1)

Description

[Tim Thome]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.6) Gecko/20050317 Firefox/1.0.2
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.6) Gecko/20050317 Firefox/1.0.2

Getting errors on simple search queries after updating to current tip. Looks
like maybe the db changes may have introduced a bug where the sql request is not
quite right. An advanced search does not appear to trigger this bug

This is with a basic install (no changes), and seeing on two machines. 

Reproducible: Always

Steps to Reproduce:
1. Sync bugzilla to 4/9/05 tip (2.19)
2. run checksetup.pl to update bugzilla
3. go to home page, select "Search existing bug reports"
4. From Find a Specific Bug tab on query.cgi, select search button

Actual Results:  
DBD::mysql::st execute failed: Unknown column 'relevance' in 'field list' [for
Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority,
bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution,
bugs.short_desc, relevance FROM bugs INNER JOIN profiles AS map_assigned_to ON
(bugs.assigned_to = map_assigned_to.userid) 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,1,8,11,12,15,14,4,2,6,13,9)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id
AND cc.who = 1 WHERE ((bugs.bug_status IN
('UNCONFIRMED','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 ORDER BY relevance desc LIMIT
200"] at /var/www/html/bugzilla2/buglist.cgi line 820

For help, please send mail to the webmaster ([e-mail]), giving this error
message and the time and date of the error. 

Expected Results:  
Should show open bugs against all products - there are bugs in the db

Output from checksetup.pl

./checksetup.pl

Checking perl modules ...
Checking for       AppConfig (v1.52)   ok: found v1.56
Checking for             CGI (v2.93)   ok: found v3.07
Checking for    Data::Dumper (any)     ok: found v2.121
Checking for    Date::Format (v2.21)   ok: found v2.22
Checking for             DBI (v1.38)   ok: found v1.48
Checking for      DBD::mysql (v2.1010) ok: found v2.9004
Checking for      File::Spec (v0.82)   ok: found v3.05
Checking for      File::Temp (any)     ok: found v0.16
Checking for        Template (v2.08)   ok: found v2.14
Checking for      Text::Wrap (v2001.0131) ok: found v2001.09292
Checking for    Mail::Mailer (v1.65)   ok: found v1.66
Checking for        Storable (any)     ok: found v2.13

The following Perl modules are optional:
Checking for              GD (v1.20)   ok: found v2.19
Checking for     Chart::Base (v1.0)    ok: found v2.3
Checking for     XML::Parser (any)     ok: found v2.34
Checking for       GD::Graph (any)     ok: found v1.43
Checking for GD::Text::Align (any)     ok: found v1.18
Checking for     PatchReader (v0.9.4)  ok: found v0.9.5

Checking user setup ...
Removing existing compiled templates ...
Precompiling templates ...
Checking for           MySQL (v3.23.41) ok: found v4.0.23-standard
Checking for        GraphViz (any)     ok: found

Populating duplicates table...

Comment 1

[Tim Thome]

The title is a bit misleading - the sql itself is bad, mysql is returning the
error based on a missing 'relevance' column. If I manually send the query it
also fails, but if I remove the references to relevance in the SELECT and ORDER
statements, the query returns correctly.

I could hack in the column into the db I suppose, but this would probably not
fix things.

Continuing to investigate...

Comment 2

[Teemu Mannermaa (:wicked)]

I can't reproduce this problem when I select open status, all product and input
nothing to words field in specific search page. Not even on a checkout from two
days ago. Generated SQL does not contain either relevance column nor ORDER BY
clause with relevance. (SQL can be seen by adding &debug=1 to the buglist URL.)

Could you provide the buglist.cgi URL generated for your query? You could also
run the buglist.cgi with &debug=1 but it should generate the same SQL as can be
seen on your error.

Comment 3

[Tim Thome]

(In reply to comment #2)
> I can't reproduce this problem when I select open status, all product and 
input
> nothing to words field in specific search page. Not even on a checkout from 
two
> days ago. Generated SQL does not contain either relevance column nor ORDER BY
> clause with relevance. (SQL can be seen by adding &debug=1 to the buglist 
URL.)
> Could you provide the buglist.cgi URL generated for your query? You could 
also
> run the buglist.cgi with &debug=1 but it should generate the same SQL as can 
be
> seen on your error.

As requested...

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, 
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, 
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, 
relevance FROM bugs INNER JOIN profiles AS map_assigned_to ON 
(bugs.assigned_to = map_assigned_to.userid) 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,1,8,11,12,15,14,4,2,6,13,9) LEFT JOIN cc ON cc.bug_id = bugs.bug_id 
AND cc.who = 1 WHERE ((bugs.bug_status IN 
('UNCONFIRMED','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 ORDER BY relevance desc LIMIT 
200

Comment 4

[Tim Thome]

(In reply to comment #2)
> I can't reproduce this problem when I select open status, all product and 
input
> nothing to words field in specific search page. Not even on a checkout from 
two
> days ago. Generated SQL does not contain either relevance column nor ORDER BY
> clause with relevance. (SQL can be seen by adding &debug=1 to the buglist 
URL.)
> Could you provide the buglist.cgi URL generated for your query? You could 
also
> run the buglist.cgi with &debug=1 but it should generate the same SQL as can 
be
> seen on your error.

Here's the URL:

http://(myhostname)/bugzilla2/buglist.cgi?
query_format=specific&order=relevance+desc&bug_status=__open__&product=&content
=

Tim

Comment 5

[Teemu Mannermaa (:wicked)]

OK, thanks. That's the same URL I tested and it does not produce relevance
column here. This is with Sticky Date: 2005.04.08.21.00.00 for buglist.cgi (rev.
1.289) and Bugzilla/Search.pm (rev. 1.93).

Could you run cvs status on these two files and report the result? Can you
update to current current cvs tip and try again?

Comment 6

[Tim Thome]

Interesting - if the "Words:" field contains text, the query passes - if left 
empty, it fails.

Below is the sql command presented when 'test' is used, and the query passes.


SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, 
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, 
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, 
(SUM(MATCH(longdescs_0.thetext) AGAINST('test'))/COUNT(MATCH
(longdescs_0.thetext) AGAINST('test')) + MATCH(bugs.short_desc) AGAINST
('test')) AS relevance FROM bugs INNER JOIN profiles AS map_assigned_to ON 
(bugs.assigned_to = map_assigned_to.userid) INNER JOIN longdescs AS 
longdescs_0 ON (bugs.bug_id = longdescs_0.bug_id ) 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,1,8,11,12,15,14,4,2,6,13,9) LEFT JOIN cc ON cc.bug_id = bugs.bug_id 
AND cc.who = 1 WHERE ((bugs.bug_status IN 
('UNCONFIRMED','NEW','ASSIGNED','REOPENED'))) AND (((MATCH
(longdescs_0.thetext) AGAINST('test') > 0) OR (lower(bugs.short_desc) 
REGEXP '(^|[^a-z0-9])test($|[^a-z0-9])'))) 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 ORDER BY relevance desc LIMIT 
200

Comment 7

[Tim Thome]

(In reply to comment #5)
> OK, thanks. That's the same URL I tested and it does not produce relevance
> column here. This is with Sticky Date: 2005.04.08.21.00.00 for buglist.cgi 
(rev.
> 1.289) and Bugzilla/Search.pm (rev. 1.93).
> Could you run cvs status on these two files and report the result? Can you
> update to current current cvs tip and try again?

Will do...

Tim

Comment 8

[Tim Thome]

As requested - CVS status on the two files:

[root@trinity bugzilla2]# cvs status Bugzilla/Search.pm
===================================================================
File: Search.pm         Status: Up-to-date

   Working revision:    1.93
   Repository revision: 
1.93    /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v
   Sticky Tag:          (none)
   Sticky Date:         (none)
   Sticky Options:      (none)

[root@trinity bugzilla2]# cvs status buglist.cgi
===================================================================
File: buglist.cgi       Status: Up-to-date

   Working revision:    1.290
   Repository revision: 
1.290   /cvsroot/mozilla/webtools/bugzilla/buglist.cgi,v
   Sticky Tag:          (none)
   Sticky Date:         (none)
   Sticky Options:      (none)

Updated to tip, cvs update -A -dP and re-ran checksetup, empty field still 
throws the error.

Tim

Comment 9

[Teemu Mannermaa (:wicked)]

(In reply to comment #6)
> Interesting - if the "Words:" field contains text, the query passes - if left 
> empty, it fails.

Yeah, this is to be expected because the relevance column is then replaced with
a fulltext search (MATCH clause and that other cruft). For some reason, when run
without any words, the meaningless relevance field is not removed from the query.

(In reply to comment #8)
> Updated to tip, cvs update -A -dP and re-ran checksetup, empty field still 
> throws the error.

OK, I'll look into this.

Comment 10

[Teemu Mannermaa (:wicked)]

Confirmed with tip install. This is actually a regression from my buglist.cgi
deFORMication work at bug 238873. Sorry. :(

This happens because line 164 now thinks we are doing fulltext search even when
empty content parameter is passed. Relevance column is not removed but because
there is no text to search the relevance column is not later replaced with
correct search. Simple fix is to remove the defined from that line to restore
previous behaviour. Patch coming up..

Comment 11

[Tim Thome]

(In reply to comment #10)
> Confirmed with tip install. This is actually a regression from my buglist.cgi
> deFORMication work at bug 238873. Sorry. :(
> This happens because line 164 now thinks we are doing fulltext search even 
when
> empty content parameter is passed. Relevance column is not removed but 
because
> there is no text to search the relevance column is not later replaced with
> correct search. Simple fix is to remove the defined from that line to restore
> previous behaviour. Patch coming up..

agreed... to confirm, backed out just the buglist.cgi file to 1.289 from 
1.290, and problem went away.

Tim

Comment 12

[Teemu Mannermaa (:wicked)]

Attachment: Simple fix that removes defined, V1

Comment 13

[Jouni Heikniemi]

Comment on attachment 180240 [details] [diff] [review]
Simple fix that removes defined, V1

r=jouni, obviously

Comment 14

[Tim Thome]

Teemu,

Put the patch into buglist, works good for my site.

Thx for the quick response.

Tim

Comment 15

[Frédéric Buclin]

Checking in buglist.cgi;
/cvsroot/mozilla/webtools/bugzilla/buglist.cgi,v  <--  buglist.cgi
new revision: 1.291; previous revision: 1.290
done