}
-# Slow but ANSI standard Limit/Offset support. DB2 uses this
+# ANSI standard Limit/Offset implementation. DB2 and MSSQL use this
sub _RowNumberOver {
my ($self, $sql, $order, $rows, $offset ) = @_;
- $offset += 1;
- my $last = $rows + $offset - 1;
- my ( $order_by ) = $self->_order_by( $order );
+ # get the order_by only (or make up an order if none exists)
+ my $order_by = $self->_order_by(
+ (delete $order->{order_by}) || $self->_rno_default_order
+ );
- $sql = <<"SQL";
-SELECT * FROM
-(
- SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM (
- $sql
- $order_by
- ) Q1
-) Q2
-WHERE ROW_NUM BETWEEN $offset AND $last
+ # whatever is left
+ my $group_having = $self->_order_by($order);
-SQL
+ my $qalias = $self->_quote ($self->{_dbic_rs_attrs}{alias});
+
+ $sql = sprintf (<<'EOS', $qalias, $order_by, $sql, $group_having, $qalias, $offset + 1, $offset + $rows, );
+SELECT * FROM (
+ SELECT %s.*, ROW_NUMBER() OVER(%s ) AS rno__row__index FROM (%s%s) %s
+) rno_subq WHERE rno__row__index BETWEEN %d AND %d
+
+EOS
+
+ $sql =~ s/\s*\n\s*/ /g; # easier to read in the debugger
return $sql;
}
-# Crappy Top based Limit/Offset support. MSSQL uses this currently,
-# but may have to switch to RowNumberOver one day
+# some databases are happy with OVER (), some need OVER (ORDER BY (SELECT (1)) )
+sub _rno_default_order {
+ return undef;
+}
+
+# Crappy Top based Limit/Offset support. Legacy from MSSQL.
sub _Top {
my ( $self, $sql, $order, $rows, $offset ) = @_;