| Subject: | [PATCH] Cannot use offset without limit |
You cannot use an offset without a limit. I don't see any particular
reason why not so attached is a patch to fix that.
This oversight is both due to explicit code in
DBIC::Storage::DBI->_execute which removes the offset if there's no
limit and due to similar code in SQL::Abstract::Limit.
The work around employed here is the one recommended in the MySQL docs.
13.2.7 SELECT syntax
...
To retrieve all rows from a certain offset up to the end of the result
set, you can use some large number for the second parameter. This
statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
I throw up a little in my mouth at this hack, but it does work and
should be safe. The real solution is to probably push this bug down
into SQL::Abstract::Limit.
| Subject: | offset_without_limit.patch |
----------------------------------------------------------------------
r27700: schwern | 2007-03-14 19:20:46 -0700
Fix search so it can accept an offset without a limit.
----------------------------------------------------------------------
=== local/DBIx-Class/t/75limit.t
==================================================================
--- local/DBIx-Class/t/75limit.t (revision 27699)
+++ local/DBIx-Class/t/75limit.t (revision 27700)
@@ -9,8 +9,8 @@
BEGIN {
eval "use DBD::SQLite";
- plan $@ ? (skip_all => 'needs DBD::SQLite for testing') : (tests => 9);
-}
+ plan $@ ? (skip_all => 'needs DBD::SQLite for testing') : (tests => 10);
+}
# test LIMIT
my $it = $schema->resultset("CD")->search( {},
@@ -51,6 +51,15 @@
);
is( $cds[0]->title, "Spoonful of bees", "software offset ok" );
+
+@cds = $schema->resultset("CD")->search( {},
+ {
+ offset => 2,
+ order_by => 'year' }
+);
+is( $cds[0]->title, "Spoonful of bees", "offset with no limit" );
+
+
# based on a failing criteria submitted by waswas
# requires SQL::Abstract >= 1.20
$it = $schema->resultset("CD")->search(
=== local/DBIx-Class/lib/DBIx/Class/Storage/DBI.pm
==================================================================
--- local/DBIx-Class/lib/DBIx/Class/Storage/DBI.pm (revision 27699)
+++ local/DBIx-Class/lib/DBIx/Class/Storage/DBI.pm (revision 27700)
@@ -832,6 +832,9 @@
} else {
$self->throw_exception("rows attribute must be positive if present")
if (defined($attrs->{rows}) && !($attrs->{rows} > 0));
+
+ # MySQL actually recommends this approach. I cringe.
+ $attrs->{rows} = 2**48 if not defined $attrs->{rows} and defined $attrs->{offset};
push @args, $attrs->{rows}, $attrs->{offset};
}
return $self->_execute(@args);