432601 - [Oracle] 'Find a specific Bug' does not work in 3.1.4

Status: RESOLVED FIXED

(Bugzilla :: Database, defect, P1)

Description

[Heiko Seitz]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.12) Gecko/20080203 SUSE/2.0.0.12-0.1 Firefox/2.0.0.12
Build Identifier: 

I've installed the development version 3.1.4 of Bugzilla in a virtual machine
running Novell openSUSE 10.3 with Oracle 9 client installed. Server runs Oracle
10g. I'm using Perl 5.8.8, DBI 1.602, DBD::Oracle 1.20, DBD-mysql 4.006.

The original database was previously running Bugzilla 3.1.3. I know about the upgrade restriction of Oracle databases but thought for myself to just check it before I recreate the whole database. The first run of checksetup.pl failed due to an error I cannot quote here because the VM crashed a short time later before I could save the console results. However, running the checksetup script a second time later (originally I wanted to see the previous error again) seemed to work perfectly. Accessing the installation with my web browser worked too.

I clicked on 'Search' and 'Find a specific Bug'. I entered a random keyword and hit the button. Instead of displaying any results, an error message appeared:

--------------------------------------

Please stand by ...
Software error:

DBD::Oracle::db prepare failed: ORA-00904: "RELEVANCE": invalid identifier (DBD ERROR: error possibly near <*> indicator at char 1260 in 'SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, (CONTAINS(bugs_fulltext_.comments_noprivate,'test') + CONTAINS(bugs_fulltext_.short_desc,'test'))  relevance FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) LEFT JOIN bugs_fulltext  bugs_fulltext_ ON (bugs.bug_id = bugs_fulltext_.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (4,13,1,10,6,11,2,8,12,5,7,3,9)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 1  WHERE  rownum <=200 AND  (( bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') ) AND (CONTAINS(bugs_fulltext_.comments_noprivate,'test') > 0 OR CONTAINS(bugs_fulltext_.short_desc,'test') > 0)) 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.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc, <*>relevance ORDER BY relevance desc /* LIMIT 200 */') [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, (CONTAINS(bugs_fulltext_.comments_noprivate,'test') + CONTAINS(bugs_fulltext_.short_desc,'test'))  relevance FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) LEFT JOIN bugs_fulltext  bugs_fulltext_ ON (bugs.bug_id = bugs_fulltext_.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (4,13,1,10,6,11,2,8,12,5,7,3,9)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 1  WHERE  rownum <=200 AND  (( bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') ) AND (CONTAINS(bugs_fulltext_.comments_noprivate,'test') > 0 OR CONTAINS(bugs_fulltext_.short_desc,'test') > 0)) 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.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc, relevance ORDER BY relevance desc /* LIMIT 200 */"] at Bugzilla/DB/Oracle.pm line 410
	Bugzilla::DB::Oracle::prepare('undef', 'SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bu...') called at /srv/www/htdocs/bugzilla/buglist.cgi line 1026

For help, please send mail to the webmaster, giving this error message and the time and date of the error.

--------------------------------------

I thought this might be a result of a failed upgrade operation of the original 3.1.3 database. So I dropped it and created a new blank one by just running checksetup.pl which worked fine. After logging in I'm encountering the same error above. 

However, this seems to be Oracle related because on a 3.1.4 mySQL database (an upgraded 2.22 installation) the 'Find a specific Bug' works fine.

Reproducible: Always

Comment 1

[Max Kanat-Alexander]

  Need someone to confirm this before it's marked as a blocker.

Comment 2

[Max Kanat-Alexander]

Confirmed on landfill.

Comment 3

[Jesse Clark]

Attachment: v1

This appears to be because relevance is not properly screened out of the GROUP BY clause. The screen was checking for fields that start with "relevance", which doesn't match "(...) AS relevance". I believe there was no problem on other databases because other parts of the screen were catching the sql_fulltext_search() syntax for those databases.

Comment 4

[Jesse Clark]

Attachment: v2

oops I didn't notice that v1 included unrelated changes to buglist.cgi

Comment 5

[Max Kanat-Alexander]

Hmm, with your patch applied I now get:

ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 243 in 'SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, (CONTAINS(<*>bugs_fulltext_.comments,'crash') + CONTAINS(bugs_fulltext_.short_desc,'crash'))  relevance FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) LEFT JOIN bugs_fulltext  bugs_fulltext_ ON (bugs.bug_id = bugs_fulltext_.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (8,16,4,9,14,2,17,3,12,10,11,13,1,6,15,5,7)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6777  WHERE  rownum <=200 AND  (( bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') ) AND (CONTAINS(bugs_fulltext_.comments,'crash') > 0 OR CONTAINS(bugs_fulltext_.short_desc,'crash') > 0)) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6777)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 6777) OR (bugs.qa_contact = 6777) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc ORDER BY relevance desc /* LIMIT 200 */') [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, (CONTAINS(bugs_fulltext_.comments,'crash') + CONTAINS(bugs_fulltext_.short_desc,'crash'))  relevance FROM bugs  INNER JOIN profiles  map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN products  map_products ON (bugs.product_id = map_products.id) LEFT JOIN bugs_fulltext  bugs_fulltext_ ON (bugs.bug_id = bugs_fulltext_.bug_id) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (8,16,4,9,14,2,17,3,12,10,11,13,1,6,15,5,7)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6777  WHERE  rownum <=200 AND  (( bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') ) AND (CONTAINS(bugs_fulltext_.comments,'crash') > 0 OR CONTAINS(bugs_fulltext_.short_desc,'crash') > 0)) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6777)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 6777) OR (bugs.qa_contact = 6777) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.op_sys, map_assigned_to.login_name, bugs.short_desc ORDER BY relevance desc /* LIMIT 200 */"] at Bugzilla/DB/Oracle.pm line 410

Comment 6

[Jesse Clark]

Any idea what's wrong with that CONTAINS() syntax?

Comment 7

[Xiaoou]

you can not "group by" when selecting a LOB :-(

Comment 8

[Max Kanat-Alexander]

(In reply to comment #7)
> you can not "group by" when selecting a LOB :-(

  Wow. What if we wrap it in a subselect or something? We're not returning the actual LOB, we're just returning a boolean.

Comment 9

[Xiaoou]

Yeah, If we put it in a subselect, it will work:-) , something like this:

select bug_id, bug_severity,priority,bug_status,resolution,op_sys, relevance from (SELECT bugs.bug_id,
       bugs.bug_severity ,
       bugs.priority ,
       bugs.bug_status,
       bugs.resolution,
       bugs.op_sys,
       map_assigned_to.login_name,  
       bugs.short_desc,
       (CONTAINS(bugs_fulltext_.comments_noprivate, 'hello') +
       CONTAINS(bugs_fulltext_.short_desc, 'hello')) relevance
  FROM bugs
 INNER JOIN profiles map_assigned_to ON (bugs.assigned_to =
                                        map_assigned_to.userid)
  LEFT JOIN bugs_fulltext bugs_fulltext_ ON (bugs.bug_id =
                                            bugs_fulltext_.bug_id)
  LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id
                         AND bug_group_map.group_id NOT IN
                             (13, 4, 1, 10, 11, 6, 2, 8, 12, 5, 3, 7, 9)
  LEFT JOIN cc ON cc.bug_id = bugs.bug_id
              AND cc.who = 1
 WHERE rownum <= 200
   AND ((bugs.bug_status IN ('UNCONFIRMED', 'NEW', 'ASSIGNED', 'REOPENED')) AND
       (CONTAINS(bugs_fulltext_.comments_noprivate, 'hello') > 0 OR
       CONTAINS(bugs_fulltext_.short_desc, 'hello') > 0))
   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 bug_id, bug_severity,priority,bug_status,resolution,op_sys, relevance order by relevance asc ;

Comment 10

[Max Kanat-Alexander]

If we can't do something about this bug I'd rather remove Oracle support and ship Bugzilla 3.2...so let's figure out what we can do about this bug. :-)

Comment 11

[Xiaoou]

Yeah, as I mentioned in Comment #9, we can do such thing in as subselect.

Comment 12

[Max Kanat-Alexander]

(In reply to comment #11)
> Yeah, as I mentioned in Comment #9, we can do such thing in as subselect.

  Look at the current code of Search.pm and tell me how that's possible. :-)

  In any case, at the worse we can disable the fulltext search functionality for Oracle Bugzilla 3.2.

Comment 13

[Xiaoou]

How about removing the "RELEVANCE" column on Oracle?

Comment 14

[Max Kanat-Alexander]

(In reply to comment #13)
> How about removing the "RELEVANCE" column on Oracle?

  That's a good idea--would that work, jjclark?

Comment 15

[Xiaoou]

It surely works. But in this way, we can not sort by relevance, will it matter?

Comment 16

[Max Kanat-Alexander]

It does matter, because we limit the results to 200 items, and they won't be a very useful 200 items if we can't search on relevance.

However, at this point I'm OK with having a useless fulltext search so that we can just release 3.2.

Comment 17

[Xiaoou]

Attachment: v3

Remove column "relevance" for Oracle, seems weird, but works.

Comment 18

[Xiaoou]

Attachment: v4

This version keeps the "relevance" column, maybe we need another form of sql_full_text

Comment 19

[Xiaoou]

Attachment: v5

This version keeps the "relevance" column, maybe we need another form of
sql_full_text

Comment 20

[Max Kanat-Alexander]

Comment on attachment 327560 [details] [diff] [review]
v5

This looks good, but instead of this, can we add a sql_fulltext_relevance to the $dbh handle and always use it in Search.pm?

Also: lable -> label

Comment 21

[Xiaoou]

Attachment: v6

Comment 22

[Max Kanat-Alexander]

Comment on attachment 327565 [details] [diff] [review]
v6

Yeah, this looks fine. :-)

Comment 23

[Max Kanat-Alexander]

tip:

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.161; previous revision: 1.160
done
Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.11; previous revision: 1.10
done

3.2:

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.159.2.2; previous revision: 1.159.2.1
done
Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.6.2.3; previous revision: 1.6.2.2
done