654496 - [Oracle] Duplicate bug detection doesn't work when using Oracle databases.

Status: RESOLVED FIXED

(Bugzilla :: Creating/Changing Bugs, defect)

Description

[Lee Millward]

User-Agent:       Mozilla/5.0 (Windows NT 5.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1
Build Identifier: 4.0.1

I'm running two Bugzilla 4.0.1 installations on Oracle 11.2 and 10.2.0.1 databases and have found that the new duplicate bug detection never returned any potential duplicates, even when testing with a database containing a couple of bugs whose summaries are were as simple as "test one" and "test two" - entering "test" into the summary field of a new bug I consistently get no possible duplicates listed.

Digging a bit deeper, I found that when I ran I dumped the SQL within possible_duplicates in Bug.pm to a temp file and ran it manually in SQL Developer, I got "ORA-00920: invalid relational operator" on the CONTAINS clause.

Adjusting that to append a > 0 on the end (so the clause reads "WHERE (CONTAINS(bugs_fulltext.short_desc,'Test',1) > 0)" instead) gets past that particular error, but I then hit "ORA-00933: SQL command not properly ended", which is complaining about the "LIMIT 12" part of the query.

I was able to get around this by replacing the "LIMIT 12" with a "AND ROWNUM <= 12", but that's pretty Oracle specific so most likely isn't the correct fix, but it did result in potential duplicates being listed when creating a new bug.

Reproducible: Always

Steps to Reproduce:
1.Enter a bug with a summary "test bug" on a Bugzilla installation running on an Oracle database (reproduced on 10.2 and 11.2 databases.

2.Start to enter a second bug and set the summary on the new bug to "test" then wait for potential duplicates to be reported.


Actual Results:  
No duplicates will be listed underneath the summary field on the second bug.

Expected Results:  
The first bug should be listed as a potential duplicate.

Comment 1

[Max Kanat-Alexander]

This may be a bug in our Oracle driver, the code it uses to figure out LIMIT may not be working here.

Comment 2

[Frédéric Buclin]

Bugzilla::Bug::possible_duplicates() uses LIMIT hardcoded instead of $dbh->sql_limit().

Comment 3

[Frédéric Buclin]

Attachment: patch, v1

This fixes the problem with Oracle 10.2.

Comment 4

[Max Kanat-Alexander]

Comment on attachment 552823 [details] [diff] [review]
patch, v1

Review of attachment 552823 [details] [diff] [review]:
-----------------------------------------------------------------

::: Bugzilla/DB/Oracle.pm
@@ +177,4 @@
>      my ($self, $column, $text, $label) = @_;
>      $text = $self->quote($text);
>      trick_taint($text);
> +    return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";

Why is CONTAINS > 0 necessary? That looks like a MySQL-ism.

Comment 5

[Frédéric Buclin]

(In reply to Max Kanat-Alexander from comment #4)
> Why is CONTAINS > 0 necessary? That looks like a MySQL-ism.

Because Oracle fails without it? :)

Comment 6

[Frédéric Buclin]

Also, examples on the Oracle website use CONTAINS(...) > 0.

Comment 7

[Max Kanat-Alexander]

Comment on attachment 552823 [details] [diff] [review]
patch, v1

Sounds good to me, then! :-)

Comment 8

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7922.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.2/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7902.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.0/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7643.