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

RESOLVED WORKSFORME

Status

()

Bugzilla
Database
RESOLVED WORKSFORME
6 years ago
5 years ago

People

(Reporter: Sunil, Unassigned)

Tracking

Details

(Reporter)

Description

6 years ago
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.
Assignee: general → database
Component: Bugzilla-General → Database
Summary: Search not functioning properly on 4.0.1 → [Oracle] Search fails due to a <*> indicator/relational operator

Comment 1

6 years ago
I cannot reproduce using Bugzilla 4.1.3. We fixed several bugs about Oracle recently. You should try to upgrade.
Status: UNCONFIRMED → RESOLVED
Last Resolved: 6 years ago
Resolution: --- → WORKSFORME
Summary: [Oracle] Search fails due to a <*> indicator/relational operator → [Oracle] Search fails when searching for two words at once
(Reporter)

Comment 2

6 years ago
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

6 years ago
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

5 years ago
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

5 years ago
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 .
You need to log in before you can comment on or make changes to this bug.