}
# and this is order re-alias magic
- for ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) {
- for my $col (keys %$_) {
+ for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) {
+ for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}} ) {
my $re_col = quotemeta ($col);
- $rno_ord =~ s/$re_col/$_->{$col}/;
+ $rno_ord =~ s/$re_col/$map->{$col}/;
}
}
Depending on the resultset attributes one of:
SELECT * FROM (
- SELECT *, ROWNUM rownum__index FROM (
+ SELECT *, ROWNUM AS rownum__index FROM (
SELECT ...
) WHERE ROWNUM <= ($limit+$offset)
) WHERE rownum__index >= ($offset+1)
or
SELECT * FROM (
- SELECT *, ROWNUM rownum__index FROM (
+ SELECT *, ROWNUM AS rownum__index FROM (
SELECT ...
)
) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
$rs_attrs->{order_by}
and
$rs_attrs->{_rsroot_rsrc}->storage->_order_by_is_stable(
- $rs_attrs->{from}, $rs_attrs->{order_by}
+ @{$rs_attrs}{qw/from order_by where/}
)
) {
push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
return <<EOS;
SELECT $sq_attrs->{selection_outer} FROM (
- SELECT $sq_attrs->{selection_outer}, ROWNUM $idx_name FROM (
+ SELECT $sq_attrs->{selection_outer}, ROWNUM AS $idx_name FROM (
SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${order_group_having}
) $qalias WHERE ROWNUM <= ?
) $qalias WHERE $idx_name >= ?
return <<EOS;
SELECT $sq_attrs->{selection_outer} FROM (
- SELECT $sq_attrs->{selection_outer}, ROWNUM $idx_name FROM (
+ SELECT $sq_attrs->{selection_outer}, ROWNUM AS $idx_name FROM (
SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${order_group_having}
) $qalias
) $qalias WHERE $idx_name BETWEEN ? AND ?
if ($sq_attrs->{order_by_requested}) {
$self->throw_exception (
'Unable to safely perform "skimming type" limit with supplied unstable order criteria'
- ) unless $rs_attrs->{_rsroot_rsrc}->schema->storage->_order_by_is_stable(
+ ) unless ($rs_attrs->{_rsroot_rsrc}->schema->storage->_order_by_is_stable(
$rs_attrs->{from},
- $requested_order
- );
+ $requested_order,
+ $rs_attrs->{where},
+ ));
$inner_order = $requested_order;
}
# and this is order re-alias magic
for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) {
- for my $col (sort { $map->{$a} cmp $map->{$b} } keys %{$map||{}}) {
+ for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}}) {
my $re_col = quotemeta ($col);
$_ =~ s/$re_col/$map->{$col}/
for ($sq_attrs->{order_by_middle}, $sq_attrs->{order_by_requested});
return $self->_GenericSubQ(@_) if $offset;
- return sprintf <<"EOF", $rows, $sql;
+ return sprintf <<"EOF", $rows, $sql, $self->_parse_rs_attrs( $rs_attrs );
SET ROWCOUNT %d
-%s
+%s %s
SET ROWCOUNT 0
EOF
}
databases. It works by ordering the set by some unique column, and calculating
the amount of rows that have a less-er value (thus emulating a L</RowNum>-like
index). Of course this implies the set can only be ordered by a single unique
-column. Also note that this technique can be and often is B<excruciatingly
-slow>.
+column.
+
+Also note that this technique can be and often is B<excruciatingly slow>. You
+may have much better luck using L<DBIx::Class::ResultSet/software_limit>
+instead.
Currently used by B<Sybase ASE>, due to lack of any other option.
my $sql_alias = (ref $s) eq 'HASH' ? $s->{-as} : undef;
push @sel, {
+ arg => $s,
sql => $sql_sel,
unquoted_sql => do {
local $self->{quote_char};
,
};
- $in_sel_index->{$sql_sel}++;
+ # anything with a placeholder in it needs re-selection
+ $in_sel_index->{$sql_sel}++ unless $sql_sel =~ / (?: ^ | \W ) \? (?: \W | $ ) /x;
+
$in_sel_index->{$self->_quote ($sql_alias)}++ if $sql_alias;
# record unqualified versions too, so we do not have
# unless we are dealing with the current source alias
# (which will transcend the subqueries as it is necessary
# for possible further chaining)
+ # same for anything we do not recognize
my ($sel, $renamed);
for my $node (@sel) {
push @{$sel->{original}}, $node->{sql};
if (
+ ! $in_sel_index->{$node->{sql}}
+ or
$node->{as} =~ / (?<! ^ $re_alias ) \. /x
or
$node->{unquoted_sql} =~ / (?<! ^ $re_alias ) $re_sep /x
}
else {
push @{$sel->{inner}}, $node->{sql};
- push @{$sel->{outer}}, $self->_quote ($node->{as});
+ push @{$sel->{outer}}, $self->_quote (ref $node->{arg} ? $node->{as} : $node->{arg});
}
}
next if $in_sel_index->{$chunk};
$extra_order_sel->{$chunk} ||= $self->_quote (
- 'ORDER__BY__' . scalar keys %{$extra_order_sel||{}}
+ 'ORDER__BY__' . sprintf '%03d', scalar keys %{$extra_order_sel||{}}
);
}