Last Comment Bug 654496 - [Oracle] Duplicate bug detection doesn't work when using Oracle databases.
: [Oracle] Duplicate bug detection doesn't work when using Oracle databases.
Status: RESOLVED FIXED
:
Product: Bugzilla
Classification: Server Software
Component: Creating/Changing Bugs (show other bugs)
: 4.0.1
: All All
: -- normal (vote)
: Bugzilla 4.0
Assigned To: Frédéric Buclin
: default-qa
:
Mentors:
: 685129 699760 702864 (view as bug list)
Depends on: 22353
Blocks:
  Show dependency treegraph
 
Reported: 2011-05-03 10:57 PDT by Lee Millward
Modified: 2011-11-16 04:21 PST (History)
7 users (show)
mkanat: approval+
mkanat: approval4.2+
LpSolit: blocking4.2+
mkanat: approval4.0+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
patch, v1 (1008 bytes, patch)
2011-08-12 19:23 PDT, Frédéric Buclin
mkanat: review+
Details | Diff | Splinter Review

Description Lee Millward 2011-05-03 10:57:50 PDT
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 2011-05-03 19:18:55 PDT
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 2011-08-12 19:10:21 PDT
Bugzilla::Bug::possible_duplicates() uses LIMIT hardcoded instead of $dbh->sql_limit().
Comment 3 Frédéric Buclin 2011-08-12 19:23:22 PDT
Created attachment 552823 [details] [diff] [review]
patch, v1

This fixes the problem with Oracle 10.2.
Comment 4 Max Kanat-Alexander 2011-08-15 16:00:55 PDT
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 2011-08-15 16:05:36 PDT
(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 2011-08-15 16:09:00 PDT
Also, examples on the Oracle website use CONTAINS(...) > 0.
Comment 7 Max Kanat-Alexander 2011-08-15 16:22:22 PDT
Comment on attachment 552823 [details] [diff] [review]
patch, v1

Sounds good to me, then! :-)
Comment 8 Frédéric Buclin 2011-08-15 18:34:17 PDT
Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7922.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.2/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7902.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.0/
modified Bugzilla/Bug.pm
modified Bugzilla/DB/Oracle.pm
Committed revision 7643.
Comment 9 Frédéric Buclin 2011-09-07 05:56:17 PDT
*** Bug 685129 has been marked as a duplicate of this bug. ***
Comment 10 Frédéric Buclin 2011-11-04 15:07:17 PDT
*** Bug 699760 has been marked as a duplicate of this bug. ***
Comment 11 Frédéric Buclin 2011-11-16 04:21:36 PST
*** Bug 702864 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.