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)
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 ?
for my $ch ($self->_order_by_chunks ($inner_order)) {
$ch = $ch->[0] if ref $ch eq 'ARRAY';
- my $is_desc = (
- $ch =~ s/\s+ ( ASC|DESC ) \s* $//ix
- and
- uc($1) eq 'DESC'
- ) ? 1 : 0;
- push @out_chunks, \join (' ', $ch, $is_desc ? 'ASC' : 'DESC' );
+ $ch =~ s/\s+ ( ASC|DESC ) \s* $//ix;
+ my $dir = uc ($1||'ASC');
+ push @out_chunks, \join (' ', $ch, $dir eq 'ASC' ? 'DESC' : 'ASC' );
}
$sq_attrs->{order_by_middle} = $self->_order_by (\@out_chunks);
# Whatever order bindvals there are, they will be realiased and
# reselected, and need to show up at end of the initial inner select
push @{$self->{select_bind}}, @{$self->{order_bind}};
+
+ # if this is a part of something bigger, we need to add back all
+ # the extra order_by's, as they may be relied upon by the outside
+ # of a prefetch or something
+ if ($rs_attrs->{_is_internal_subuery}) {
+ $sq_attrs->{selection_outer} .= sprintf ", $extra_order_sel->{$_} AS $_"
+ for sort
+ { $extra_order_sel->{$a} cmp $extra_order_sel->{$b} }
+ grep { $_ !~ /[^\w\-]/ } # ignore functions
+ keys %$extra_order_sel
+ ;
+ }
}
# and this is order re-alias magic
. 'unique-column order criteria.'
);
- my $direction = (
- $first_order_by =~ s/\s+ ( ASC|DESC ) \s* $//ix
- ) ? lc($1) : 'asc';
+ $first_order_by =~ s/\s+ ( ASC|DESC ) \s* $//ix;
+ my $direction = lc ($1 || 'asc');
my ($first_ord_alias, $first_ord_col) = $first_order_by =~ /^ (?: ([^\.]+) \. )? ([^\.]+) $/x;