702864 - Oracle sql_limit and CONTAINS() fix

Status: RESOLVED DUPLICATE of bug 654496

(Bugzilla :: Database, defect)

Description

[Kevin]

User Agent: Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.120 Safari/535.2

Steps to reproduce:

diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index 0819bd1..1fb7c2b 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -142,12 +142,15 @@ sub sql_not_regexp {
 }
 
 sub sql_limit {
-    my ($self, $limit, $offset) = @_;
+    my ($self, $limit, $offset, $query) = @_;
 
-    if(defined $offset) {
+    if(defined $offset && $offset !=0) {
+        my $last = $offset+$limit;
+        return "select * from ($query) where ROWNUM > $offset AND ROWNUM < $last";
         return  "/* LIMIT $limit $offset */";
     }
-    return "/* LIMIT $limit */";
+    
+    return "select * from ($query) where ROWNUM <= $limit";
 }
 
 sub sql_string_concat {
@@ -177,7 +180,7 @@ sub sql_fulltext_search {
     my ($self, $column, $text, $label) = @_;
     $text = $self->quote($text);
     trick_taint($text);
-    return "CONTAINS($column,$text,$label)", "SCORE($label)";
+    return "CONTAINS($column,$text,$label)=1", "SCORE($label)";
 }
 
 sub sql_date_format {



Actual results:

I noticed bug 300281 was listed in WONTFIX but the patch included has been working for me so far with Oracle and limits.

Also, I kept getting a "DBD::Oracle::db prepare failed: ORA-00920: invalid relational operator" error when searching for anything beside a bug number.  I'm using Oracle 10g and I had to add a '=1' to evaluate CONTAINS() to be true. 


Expected results:

Limits and searches should work.

Comment 1

[Kevin]

and I just realized that "What I did" meant how to reproduce and not what I did to fix the problem.

To see limits work I had used a modified buglist.cgi that set $query using $dbh->sql_limit and passed in limit on the URL.

For CONTAINS I searched on random text contained in bug subjects from the main page.

Comment 2

[Frédéric Buclin]

You are reporting two problems which are already fixed in Bugzilla 4.0.3 (to be released in the coming days), see bug 582209 and bug 654496.

Comment 3

[Kevin]

Awesome, glad they are already in the next release.  Sorry for the duplicate.