Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 66390
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: RWTNORTON [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 0.08127
Fixed in: 0.08191



Subject: joined group_by count resultset can produce long column alias for Oracle
Download (untitled) / with headers
text/plain 1.2k
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.
Download (untitled) / with headers
text/plain 279b
Can you please turn this into a proper extra test case in t/73oracle.t. Right around the blocks starting on line 212: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/73oracle.t;h=45f615295160e30c3e6a4c7284de9bc90d802b03;hb=HEAD#l212 Thank you!
Download (untitled) / with headers
text/plain 191b
Okay. Made changes to 73oracle.t on a clone of the repo and sent a pull request on github. Not entirely certain that was the right thing to do, so attaching the diff. Many thanks, Richard
Subject: 73oracle.t.diff
Download 73oracle.t.diff
text/x-diff 1.5k
222a223,265 > # test rel names over the 30 char limit using group_by and join > { > my @group_cols = ( 'me.name' ); > my $query = $schema->resultset('Artist')->search({ > artistid => 1 > }, { > select => \@group_cols, > as => [map { /^\w+\.(\w+)$/ } @group_cols], > join => [qw( cds_very_very_very_long_relationship_name )], > group_by => \@group_cols, > }); > > lives_and { > my @got = $query->get_column('name')->all(); > is_deeply \@got, [$new_artist->name]; > } 'query with rel name over 30 chars worked on join, group_by for me col'; > > lives_and { > is $query->count(), 1 > } 'query with rel name over 30 chars worked on join, group_by, count for me col'; > } > { > local $TODO = 'group_by on rel longer than 30 chars for long rel cols'; > my @group_cols = ( 'cds_very_very_very_long_relationship_name.title' ); > my $query = $schema->resultset('Artist')->search({ > artistid => 1 > }, { > select => \@group_cols, > as => [map { /^\w+\.(\w+)$/ } @group_cols], > join => [qw( cds_very_very_very_long_relationship_name )], > group_by => \@group_cols, > }); > > lives_and { > my @got = $query->get_column('title')->all(); > is_deeply \@got, [$new_cd->title]; > } 'query with rel name over 30 chars worked on join, group_by for long rel col'; > > lives_and { > is $query->count(), 1 > } 'query with rel name over 30 chars worked on join, group_by, count for long rel col'; > } >
Download (untitled) / with headers
text/plain 473b
On Tue Mar 08 01:48:14 2011, RWTNORTON wrote: Show quoted text
> Okay. Made changes to 73oracle.t on a clone of the repo and sent a pull > request on github. Not entirely certain that was the right thing to do, > so attaching the diff. >
Pull reqs are fine as well yes. Fixed in: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=e527dbbc28335c18d86908bde879d8967984f1d4 Should release within couple of days (if the yak shaving marathon goes well :)
The fix has finally been release as DBIC 0.08191


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.