685129 - [Oracle] Quick/Simple search fails with ORA-00920 invalid relational operator

Status: RESOLVED DUPLICATE of bug 654496

(Bugzilla :: Database, defect)

Description

[Lambros Sakellariou]

Attachment: Full error message for ORA-00920

After I upgraded Bugzilla from version 3.6 to version 4.0.2 Simple search and Quick search failed with the following error:

DBD::Oracle::db prepare failed: ORA-00920: invalid relational operator (DBD ERROR: error possibly near <*> indicator at char 668 in 'SELECT bugs.bug_id  bug_id, bugs.bug_severity  bug_severity, bugs.priority  priority.....
(Full error attached)

Database version:Oracle 10.2.0.4

Comment 1

[Lambros Sakellariou]

Checking the sql statement I found out that it used CONTAINS as follow:
...
where 
...
   or (instr(lower(bugs.status_whiteboard), lower('xxxx')) > 0)
   or (contains(bugs_fulltext_0.comments_noprivate,'"xxxx"',1)  
        or contains(bugs_fulltext_0.short_desc,'"xxxx"',2)))
...

which is invalid syntax as CONTAINS returns a number.
The correct would be

  or (instr(lower(bugs.status_whiteboard), lower('google')) > 0)
  or (contains(bugs_fulltext_0.comments_noprivate,'"google"',1) > 0 
        OR CONTAINS(bugs_fulltext_0.short_desc,'"google"',2) > 0))


Possible solution:
I edited the sql_fulltext_search subroutine in Oracle.pm and i changed line 180 from :    return "CONTAINS($column,$text,$label)", "SCORE($label)";
  to :    return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";

That fixed it for me.

Comment 2

[Frédéric Buclin]

Already fixed by bug 654496.