Optimize RowNum limit dialect as per suggestion in RT#61277

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / SQLMaker / LimitDialects.pm

diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
index d4689a7..723001d 100644 (file)
--- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm
+++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
@@ -266,8 +266,8 @@ sub _FirstSkip {
  SELECT * FROM (
   SELECT *, ROWNUM rownum__index FROM (
    SELECT ...
-  )
- ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
+  ) WHERE ROWNUM <= ($limit+$offset)
+ ) WHERE rownum__index >= ($offset+1)
 
 Supported by B<Oracle>.
 
@@ -285,15 +285,27 @@ sub _RowNum {
   my $idx_name = $self->_quote ('rownum__index');
   my $order_group_having = $self->_parse_rs_attrs($rs_attrs);
 
-  $sql = sprintf (<<EOS, $offset + 1, $offset + $rows, );
+  if ($offset) {
+
+    $sql = sprintf (<<EOS, $offset + $rows, $offset + 1 );
 
 SELECT $outsel FROM (
   SELECT $outsel, ROWNUM $idx_name FROM (
     SELECT $insel ${sql}${order_group_having}
-  ) $qalias
-) $qalias WHERE $idx_name BETWEEN %u AND %u
+  ) $qalias WHERE ROWNUM <= %u
+) $qalias WHERE $idx_name >= %u
 
 EOS
+  }
+  else {
+    $sql = sprintf (<<EOS, $rows );
+
+  SELECT $outsel FROM (
+    SELECT $insel ${sql}${order_group_having}
+  ) $qalias WHERE ROWNUM <= %u
+
+EOS
+  }
 
   $sql =~ s/\s*\n\s*/ /g;   # easier to read in the debugger
   return $sql;
@@ -590,7 +602,6 @@ EOS
 # also return a hashref (order doesn't matter) of QUOTED EXTRA-SEL =>
 # QUOTED ALIAS pairs, which is a list of extra selectors that do *not*
 # exist in the original select list
-
 sub _subqueried_limit_attrs {
   my ($self, $rs_attrs) = @_;
 
@@ -610,7 +621,6 @@ sub _subqueried_limit_attrs {
     my $sql_sel = $self->_recurse_fields ($s);
     my $sql_alias = (ref $s) eq 'HASH' ? $s->{-as} : undef;
 
-
     push @sel, {
       sql => $sql_sel,
       unquoted_sql => do { local $self->{quote_char}; $self->_recurse_fields ($s) },