300865 - postgresql 8.0.3 doesn't like sql for simple query

Status: RESOLVED DUPLICATE of bug 298400

(Bugzilla :: Query/Bug List, defect)

Description

[bruce robertson]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02
Build Identifier: 2.20rc1


Please stand by ...
Content-type: text/html
Software error:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "desc" at character 1489
 [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority,
map_products.name, map_components.name, bugs.short_desc, (SUM(CASE WHEN
(LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END)/COUNT(CASE
WHEN (LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END) + CASE
WHEN (LOWER(bugs.short_desc) LIKE '%autopatch%') THEN 1 ELSE 0 END) AS relevance
FROM bugs  INNER JOIN products AS map_products ON (bugs.product_id =
map_products.id) INNER JOIN components AS map_components ON (bugs.component_id =
map_components.id) 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 (3,7,10,5,1,8,11,4,2,6,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 (bugs.product_id IN (4))) AND
(((CASE WHEN (LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END >
0) OR (bugs.short_desc ~* '(^|[^a-z0-9])autopatch($|[^a-z0-9])'))) AND
bugs.creation_ts IS NOT NULL 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) ) GROUP BY bugs.bug_id, bugs.short_desc, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, map_products.name, map_components.name,
relevance desc ORDER BY relevance desc LIMIT 200"] at
/auto/gsg-sw/inst/gnudatadir/cgi-bin/bugzilla-2.20rc1/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. 

Reproducible: Always

Steps to Reproduce:
1.http://florence:8080/cgi-bin/bugzilla-2.20rc1/query.cgi?format=specific
2. enter status/product/words
3.click search button

Actual Results:  

Please stand by ...
Content-type: text/html
Software error:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "desc" at character 1489
 [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority,
map_products.name, map_components.name, bugs.short_desc, (SUM(CASE WHEN
(LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END)/COUNT(CASE
WHEN (LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END) + CASE
WHEN (LOWER(bugs.short_desc) LIKE '%autopatch%') THEN 1 ELSE 0 END) AS relevance
FROM bugs  INNER JOIN products AS map_products ON (bugs.product_id =
map_products.id) INNER JOIN components AS map_components ON (bugs.component_id =
map_components.id) 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 (3,7,10,5,1,8,11,4,2,6,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 (bugs.product_id IN (4))) AND
(((CASE WHEN (LOWER(longdescs_0.thetext) LIKE '%autopatch%') THEN 1 ELSE 0 END >
0) OR (bugs.short_desc ~* '(^|[^a-z0-9])autopatch($|[^a-z0-9])'))) AND
bugs.creation_ts IS NOT NULL 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) ) GROUP BY bugs.bug_id, bugs.short_desc, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, map_products.name, map_components.name,
relevance desc ORDER BY relevance desc LIMIT 200"] at
/auto/gsg-sw/inst/gnudatadir/cgi-bin/bugzilla-2.20rc1/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:  
show damn bug

I pasted this query into psql WITHOUT the "relevance desc" BEFORE the "ORDER BY"
and it worked. I don't understand what those two things are doing there in the sql.

Comment 1

[Max Kanat-Alexander]

*** This bug has been marked as a duplicate of 298400 ***