);
}
+
=head2 RowNum
+Depending on the resultset attributes one of:
+
SELECT * FROM (
SELECT *, ROWNUM rownum__index FROM (
SELECT ...
) WHERE ROWNUM <= ($limit+$offset)
) WHERE rownum__index >= ($offset+1)
+or
+
+ SELECT * FROM (
+ SELECT *, ROWNUM rownum__index FROM (
+ SELECT ...
+ )
+ ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
+
+or
+
+ SELECT * FROM (
+ SELECT ...
+ ) WHERE ROWNUM <= ($limit+1)
+
Supported by B<Oracle>.
=cut
my $idx_name = $self->_quote ('rownum__index');
my $order_group_having = $self->_parse_rs_attrs($rs_attrs);
+ #
+ # There are two ways to limit in Oracle, one vastly faster than the other
+ # on large resultsets: https://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/
+ # However Oracle is retarded and does not preserve stable ROWNUM() values
+ # when called twice in the same scope. Therefore unless the resultset is
+ # ordered by a unique set of columns, it is not safe to use the faster
+ # method, and the slower BETWEEN query is used instead
+ #
+ # FIXME - this is quite expensive, and doe snot perform caching of any sort
+ # as soon as some of the DQ work becomes viable consider switching this
+ # over
+ if ( __order_by_is_unique($rs_attrs) ) {
+
+ # if offset is 0 (first page) the we can skip a subquery
+ if (! $offset) {
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+
+ return <<EOS;
+SELECT $outsel FROM (
+ SELECT $insel ${stripped_sql}${order_group_having}
+) $qalias WHERE ROWNUM <= ?
+EOS
+ }
+ else {
+ push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
- if ($offset) {
-
- push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
-
- return <<EOS;
+ return <<EOS;
SELECT $outsel FROM (
SELECT $outsel, ROWNUM $idx_name FROM (
SELECT $insel ${stripped_sql}${order_group_having}
) $qalias WHERE ROWNUM <= ?
) $qalias WHERE $idx_name >= ?
EOS
-
+ }
}
else {
- push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset + 1 ], [ $self->__total_bindtype => $offset + $rows ];
return <<EOS;
- SELECT $outsel FROM (
+SELECT $outsel FROM (
+ SELECT $outsel, ROWNUM $idx_name FROM (
SELECT $insel ${stripped_sql}${order_group_having}
- ) $qalias WHERE ROWNUM <= ?
+ ) $qalias
+) $qalias WHERE $idx_name BETWEEN ? AND ?
EOS
+ }
+}
+
+# determine if the supplied order_by contains a unique column (set)
+sub __order_by_is_unique {
+ my $rs_attrs = shift;
+ my $rsrc = $rs_attrs->{_rsroot_rsrc};
+ my $order_by = $rs_attrs->{order_by}
+ || return 0;
+
+ my $storage = $rsrc->schema->storage;
+ my @order_by_cols = map { $_->[0] } $storage->_extract_order_criteria($order_by)
+ or return 0;
+
+ my $colinfo =
+ $storage->_resolve_column_info($rs_attrs->{from}, \@order_by_cols);
+
+ my $sources = {
+ map {( "$_" => $_ )} map { $_->{-result_source} } values %$colinfo
+ };
+
+ my $supplied_order = {
+ map { $_ => 1 }
+ grep { exists $colinfo->{$_} and ! $colinfo->{$_}{is_nullable} }
+ @order_by_cols
+ };
+
+ return 0 unless keys %$supplied_order;
+
+ for my $uks (
+ map { values %$_ } map { +{ $_->unique_constraints } } values %$sources
+ ) {
+ return 1
+ unless first { ! exists $supplied_order->{$_} } @$uks;
}
+
+ return 0;
}
-# used by _Top and _FetchFirst
+# used by _Top and _FetchFirst below
sub _prep_for_skimming_limit {
my ( $self, $sql, $rs_attrs ) = @_;
my $rs = $schema->resultset('Artist')->search({}, {
start_with => { name => 'root' },
connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
- order_by => { -asc => 'name' },
+ order_by => [ { -asc => 'name' }, { -desc => 'artistid' } ],
rows => 2,
});
FROM artist me
START WITH name = ?
CONNECT BY parentid = PRIOR artistid
- ORDER BY name ASC
+ ORDER BY name ASC, artistid DESC
) me
WHERE ROWNUM <= ?
)',
FROM (
SELECT artistid
FROM (
- SELECT me.artistid
- FROM artist me
- START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ SELECT artistid, ROWNUM rownum__index
+ FROM (
+ SELECT me.artistid
+ FROM artist me
+ START WITH name = ?
+ CONNECT BY parentid = PRIOR artistid
+ ) me
) me
- WHERE ROWNUM <= ?
+ WHERE rownum__index BETWEEN ? AND ?
) me
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
- => 'root'], [ $ROWS => 2 ] ,
+ => 'root'],
+ [ $ROWS => 1 ],
+ [ $TOTAL => 2 ],
],
);
my $rs = $s->resultset ('CD');
-is_same_sql_bind (
- $rs->search ({}, { rows => 1, offset => 3,columns => [
- { id => 'foo.id' },
- { 'bar.id' => 'bar.id' },
- { bleh => \ 'TO_CHAR (foo.womble, "blah")' },
- ]})->as_query,
- '(
- SELECT id, bar__id, bleh
+for my $test_set (
+ {
+ name => 'Rownum subsel aliasing works correctly',
+ rs => $rs->search_rs(undef, {
+ rows => 1,
+ offset => 3,
+ columns => [
+ { id => 'foo.id' },
+ { 'bar.id' => 'bar.id' },
+ { bleh => \'TO_CHAR (foo.womble, "blah")' },
+ ]
+ }),
+ sql => '(
+ SELECT id, bar__id, bleh
FROM (
SELECT id, bar__id, bleh, ROWNUM rownum__index
- FROM (
- SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
- FROM cd me
- ) me
+ FROM (
+ SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR (foo.womble, "blah") AS bleh
+ FROM cd me
+ ) me
+ ) me WHERE rownum__index BETWEEN ? AND ?
+ )',
+ binds => [
+ [ $OFFSET => 4 ],
+ [ $TOTAL => 4 ],
+ ],
+ }, {
+ name => 'Rownum subsel aliasing works correctly with unique order_by',
+ rs => $rs->search_rs(undef, {
+ rows => 1,
+ offset => 3,
+ columns => [
+ { id => 'foo.id' },
+ { 'bar.id' => 'bar.id' },
+ { bleh => \'TO_CHAR (foo.womble, "blah")' },
+ ],
+ order_by => [qw( artist title )],
+ }),
+ sql => '(
+ SELECT id, bar__id, bleh
+ FROM (
+ SELECT id, bar__id, bleh, ROWNUM rownum__index
+ FROM (
+ SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
+ FROM cd me
+ ORDER BY artist, title
+ ) me
WHERE ROWNUM <= ?
) me
- WHERE rownum__index >= ?
- )',
- [
- [ $TOTAL => 4 ],
- [ $OFFSET => 4 ],
- ],
- 'Rownum subsel aliasing works correctly'
-);
-
-is_same_sql_bind (
- $rs->search ({}, { rows => 2, offset => 3,columns => [
- { id => 'foo.id' },
- { 'ends_with_me.id' => 'ends_with_me.id' },
- ]})->as_query,
- '(SELECT id, ends_with_me__id
+ WHERE rownum__index >= ?
+ )',
+ binds => [
+ [ $TOTAL => 4 ],
+ [ $OFFSET => 4 ],
+ ],
+ }, {
+ name => 'Rownum subsel aliasing #2 works correctly',
+ rs => $rs->search_rs(undef, {
+ rows => 2,
+ offset => 3,
+ columns => [
+ { id => 'foo.id' },
+ { 'ends_with_me.id' => 'ends_with_me.id' },
+ ]
+ }),
+ sql => '(
+ SELECT id, ends_with_me__id
FROM (
SELECT id, ends_with_me__id, ROWNUM rownum__index
- FROM (
- SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
- FROM cd me
- ) me
+ FROM (
+ SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
+ FROM cd me
+ ) me
+ ) me WHERE rownum__index BETWEEN ? AND ?
+ )',
+ binds => [
+ [ $OFFSET => 4 ],
+ [ $TOTAL => 5 ],
+ ],
+ }, {
+ name => 'Rownum subsel aliasing #2 works correctly with unique order_by',
+ rs => $rs->search_rs(undef, {
+ rows => 2,
+ offset => 3,
+ columns => [
+ { id => 'foo.id' },
+ { 'ends_with_me.id' => 'ends_with_me.id' },
+ ],
+ order_by => [qw( artist title )],
+ }),
+ sql => '(
+ SELECT id, ends_with_me__id
+ FROM (
+ SELECT id, ends_with_me__id, ROWNUM rownum__index
+ FROM (
+ SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
+ FROM cd me
+ ORDER BY artist, title
+ ) me
WHERE ROWNUM <= ?
) me
- WHERE rownum__index >= ?
- )',
- [
- [ $TOTAL => 5 ],
- [ $OFFSET => 4 ],
- ],
- 'Rownum subsel aliasing works correctly'
-);
+ WHERE rownum__index >= ?
+ )',
+ binds => [
+ [ $TOTAL => 5 ],
+ [ $OFFSET => 4 ],
+ ],
+ }
+) {
+ is_same_sql_bind(
+ $test_set->{rs}->as_query,
+ $test_set->{sql},
+ $test_set->{binds},
+ $test_set->{name});
+}
{
my $subq = $s->resultset('Owners')->search({
JOIN owners owner ON owner.id = me.owner
WHERE ( source = ? )
) me
- WHERE ROWNUM <= ?
) me
- WHERE rownum__index >= ?
+ WHERE rownum__index BETWEEN ? AND ?
)',
[
[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
=> 'Library' ],
- [ $TOTAL => 5 ],
[ $OFFSET => 4 ],
+ [ $TOTAL => 5 ],
],
'pagination with subquery works'
is_same_sql_bind(
$rs_selectas_rel->as_query,
- '( SELECT id, owner FROM (
- SELECT me.id, me.owner
- FROM books me
- WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) )
- ) me WHERE ROWNUM <= ?
- )',
+ '(
+ SELECT id, owner FROM (
+ SELECT id, owner, ROWNUM rownum__index FROM (
+ SELECT me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) )
+ ) me
+ ) me WHERE rownum__index BETWEEN ? AND ?
+ )',
[
[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $OFFSET => 1 ],
[ $TOTAL => 1 ],
],
'Pagination with sub-query in WHERE works'
projects / dbsrgits/DBIx-Class.git / commitdiff