Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 25445
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: mschwern [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Unimportant
Broken in: (no value)
Fixed in: (no value)



Subject: [PATCH] Cannot use offset without limit
Download (untitled) / with headers
text/plain 804b
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);
Already applied in svn. Now in 0.08/trunk.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.