| Subject: | joined group_by count resultset can produce long column alias for Oracle |
The column aliases automatically produced by DBIx::Class::ResultSet
method _count_subq_rs can exceed the thirty character limit of Oracle
identifiers. Specifically, here:
# unqualify join-based group_by's. Arcane but possible query
# also horrible horrible hack to alias a column (not a func.)
# (probably need to introduce SQLA syntax)
if ($colpiece =~ /\./ && $colpiece !~ /^$attrs->{alias}\./) {
my $as = $colpiece;
$as =~ s/\./__/;
$colpiece = \ sprintf ('%s AS %s', map { $sql_maker->_quote ($_)
} ($col
piece, $as) );
}
I've seen the issue arise when performing a search() performing a join
with group_by ending in a call to count(). This can happen if the
relationship name concatenated with "__" concatenated with any column of
the joined table exceeds 30 characters.
package A
...
belongs_to 'some_rel_name', That::One::ResultClass::B;
...
package B
...
add_columns(
...
'some_longish_column',
...
);
...
my $count = $schema->resultset('A')->search( \%search, {
join => [qw( some_rel_name )],
group_by => \@whatever,
})->count();
Produces:
select count(*) from (
select some_rel_name.some_longish_column AS
some_rel_name__some_longish_column, ... from a join b on ...
) ...
"some_rel_name__some_longish_column" causes Oracle to freak out.