682250 - [Oracle] Search fails when searching for two words at once

Status: RESOLVED WORKSFORME

(Bugzilla :: Database, defect)

Description

[Sunil]

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E; OfficeLiveConnector.1.3; OfficeLivePatch.0.0; BRI/2)

Steps to reproduce:

I have tried to search for a bug entering key words.


Actual results:

I got a page with following error:

Software error:
DBD::Oracle::db prepare failed: ORA-00920: invalid relational operator (DBD ERROR: error possibly near <*> indicator at char 898 in 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, bugs.op_sys  op_sys, map_assigned_to.login_name  assigned_to, bugs.short_desc  short_desc 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_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_status ON (bug_status.value = bugs.bug_status) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (1,10,11,13,12,9,4,8,5,6,7,3,2)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 1 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1<*>=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('bug')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('bug')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('bug')) > 0) OR (CONTAINS(bugs_fulltext_0.comments_noprivate,'"bug"',1) OR CONTAINS(bugs_fulltext_0.short_desc,'"bug"',2)))) 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) OR (bugs.qa_contact = 1) ) 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, bug_status.sortkey, bug_status.value, priority.sortkey, priority.value ORDER BY bug_status.sortkey,bug_status.value,priority.sortkey,priority.value,assigned_to,bug_id') [for Statement "SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority, bugs.bug_status  bug_status, bugs.resolution  resolution, map_products.name  product, bugs.op_sys  op_sys, map_assigned_to.login_name  assigned_to, bugs.short_desc  short_desc 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_0 ON (bugs.bug_id = bugs_fulltext_0.bug_id) LEFT JOIN bug_status ON (bug_status.value = bugs.bug_status) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (1,10,11,13,12,9,4,8,5,6,7,3,2)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 1 WHERE (( bugs.bug_status IN ('REOPENED','NEW','ASSIGNED','UNCONFIRMED') )) AND (((1=2) OR (1=2) OR (INSTR(LOWER(COALESCE(bugs.alias, '__BZ_EMPTY_STR__')), LOWER('bug')) > 0) OR (INSTR(LOWER(bugs.short_desc), LOWER('bug')) > 0) OR (INSTR(LOWER(bugs.status_whiteboard), LOWER('bug')) > 0) OR (CONTAINS(bugs_fulltext_0.comments_noprivate,'"bug"',1) OR CONTAINS(bugs_fulltext_0.short_desc,'"bug"',2)))) 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) OR (bugs.qa_contact = 1) ) 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, bug_status.sortkey, bug_status.value, priority.sortkey, priority.value ORDER BY bug_status.sortkey,bug_status.value,priority.sortkey,priority.value,assigned_to,bug_id"] at Bugzilla/DB/Oracle.pm line 495
	Bugzilla::DB::Oracle::prepare('undef', 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, ...') called at /var/www/html/bugzilla/buglist.cgi line 907


Expected results:

It should provide the results matching the key words.

Comment 1

[Frédéric Buclin]

I cannot reproduce using Bugzilla 4.1.3. We fixed several bugs about Oracle recently. You should try to upgrade.

Comment 2

[Sunil]

Thanks for your information Frederic Buclin.

Could you please confirm this issue has been fixed in 4.0.2 as it is more stable version as per website?

Or else could we update the Bugzilla installation to 4.1.3 without any issues?

Comment 3

[dhall]

We fixed it by editing the file Bugzilla/DB/Oracle.pm
changing the line:
return "CONTAINS($column,$text,$label)", "SCORE($label)";
to:
return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";

We found the same fix here as well:
http://bzr.mozilla.org/bugzilla/4.0/revision/7643/Bugzilla/DB/Oracle.pm

Comment 4

[Peter Hawkins]

I'm hitting this problem on version 4.2.1 . I've had a look at Oracle.pm and it's got the "> 0" line there correctly, so I'm puzzled as to why I'm getting the ORA-29907 error.

Anyone else seeing this?

Comment 5

[Peter Hawkins]

I've figured out a cheap and nasty workaround:
replace:

    return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";

with:

    return "dbms_lob.instr(lower($column),lower($text)) > 0";

in Oracle.pm .