# make up an order if none exists
my $requested_order = (delete $rs_attrs->{order_by}) || $self->_rno_default_order;
+
+ # the order binds (if any) will need to go at the end of the entire inner select
+ local $self->{order_bind};
my $rno_ord = $self->_order_by ($requested_order);
+ push @{$self->{select_bind}}, @{$self->{order_bind}};
# this is the order supplement magic
my $mid_sel = $sq_attrs->{selection_outer};
}
# 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}/;
}
}
# 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});
$lim->{order_by_middle},
) if $offset;
- $sql = sprintf ('SELECT TOP %u %s FROM ( %s ) %s %s',
- $rows,
+ $sql = sprintf ('SELECT %s FROM ( %s ) %s %s',
$lim->{selection_outer},
$sql,
$lim->{quoted_rs_alias},
) if $offset;
- $sql = sprintf ('SELECT %s FROM ( %s ) %s %s FETCH FIRST %u ROWS ONLY',
+ $sql = sprintf ('SELECT %s FROM ( %s ) %s %s',
$lim->{selection_outer},
$sql,
$lim->{quoted_rs_alias},
$lim->{order_by_requested},
- $rows,
) if $offset and (
$lim->{order_by_requested} or $lim->{selection_middle} ne $lim->{selection_outer}
);
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
}
my ($first_order_by) = do {
local $self->{quote_char};
+ local $self->{order_bind};
map { ref $_ ? $_->[0] : $_ } $self->_order_by_chunks ($rs_attrs->{order_by})
} or $self->throw_exception (
'Generic Subquery Limit does not work on resultsets without an order. Provide a single, '
"Generic Subquery Limit first order criteria '$first_ord_col' must be unique"
) unless $root_rsrc->_identifying_column_set([$first_ord_col]);
- my $sq_attrs = $self->_subqueried_limit_attrs ($sql, $rs_attrs);
+ my $sq_attrs = do {
+ # perform the mangling only using the very first order crietria
+ # (the one we care about)
+ local $rs_attrs->{order_by} = $first_order_by;
+ $self->_subqueried_limit_attrs ($sql, $rs_attrs);
+ };
my $cmp_op = $direction eq 'desc' ? '>' : '<';
my $count_tbl_alias = 'rownum__emulation';
- my $order_sql = $self->_order_by (delete $rs_attrs->{order_by});
+ my ($order_sql, @order_bind) = do {
+ local $self->{order_bind};
+ my $s = $self->_order_by (delete $rs_attrs->{order_by});
+ ($s, @{$self->{order_bind}});
+ };
my $group_having_sql = $self->_parse_rs_attrs($rs_attrs);
my $in_sel = $sq_attrs->{selection_inner};
;
}
+ # even though binds in order_by make no sense here (the rs needs to be
+ # ordered by a unique column first) - pass whatever there may be through
+ # anyway
+ push @{$self->{limit_bind}}, @order_bind;
+
return sprintf ("
SELECT $sq_attrs->{selection_outer}
FROM (
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});
}
}