Skip Menu |
 

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

Report information
The Basics
Id: 107251
Status: open
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: martin.spevak [...] hpe.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: missing group by in final query
Date: Tue, 22 Sep 2015 20:27:05 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Martin Spevak <martin.spevak [...] hpe.com>
Download (untitled) / with headers
text/plain 2.4k
Hello. I found issue during resultset delete. Here is broken construction: $radius->resultset('RadGroup')->search(undef, { select => [ 'me.groupname' ], join => [ qw/check_clear/ ], group_by=> { -asc => 'me.groupname }, having => 'count(check_clear.groupname) = 0', })->delete(); #I tried also group_by => 'me.groupname' Here is constructed query with missing group by part (I got it using DBIC_TRACE): DELETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupname FROM radgroups me LEFT JOIN radgroupcheck check_clear ON check_clear.groupname = me.groupname HAVING count(check_clear.groupname) = 0 ) ): with next error message: message: DBI Exception: DBD::Pg::st execute failed: ERROR: column "me.groupname" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...LETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupna... ^ [for Statement "DELETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupname FROM radgroups me LEFT JOIN radgroupcheck check_clear ON check_clear.groupname = me.groupname HAVING count(check_clear.groupname) = 0 ) )"] at /usr/share/perl5/vendor_perl/DBIx/Class/Schema.pm line 1077 In RadGroup.pm I have: __PACKAGE__->has_many( "check_clear", "HP_Infrasec::Models::Radius::Result::RadGroupCheck", { "foreign.groupname" => "self.groupname" }, { cascade_copy => 0, cascade_delete => 0, join_type => 'LEFT' }, ); This is temporary working solution: my $tmp_radgroup = $radius->resultset('RadGroup')->search(undef, { select => [ 'me.groupname' ], join => [ qw/check_clear/ ], group_by=> 'me.groupname', having => 'count(check_clear.groupname) = 0', })->get_column('me.groupname'); $radius->resultset('RadGroup')->search({ 'me.groupname' => { IN => $tmp_radgroup->as_query }, })->delete(); provides: DELETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupname FROM radgroups me LEFT JOIN radgroupcheck check_clear ON check_clear.groupname = me.groupname GROUP BY me.groupname HAVING count(check_clear.groupname) = 0 ) ): System information: perl -v This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi DBIx version: $VERSION = '0.082820'; -- *Martin (singer) Spevak* HPES Software Development Engineer HPES Network Management Solutions Location: Galvaniho 7/A, Bratislava, Slovakia Tel.: +421 2 5752 5390 Email: martin.spevak@hp.com
Subject: Re: [rt.cpan.org #107251] missing group by in final query
Date: Tue, 22 Sep 2015 20:39:06 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 2.6k
On 09/22/2015 08:27 PM, Martin Spevak via RT wrote: Show quoted text
> Tue Sep 22 14:27:19 2015: Request 107251 was acted upon. > Transaction: Ticket created by martin.spevak@hpe.com > Queue: DBIx-Class > Subject: missing group by in final query > Broken in: (no value) > Severity: (no value) > Owner: Nobody > Requestors: martin.spevak@hpe.com > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=107251 > > > > Hello. > > I found issue during resultset delete. Here is broken construction: > > $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> { -asc => 'me.groupname }, > having => 'count(check_clear.groupname) = 0', > })->delete(); > #I tried also group_by => 'me.groupname'
group_by=> { -asc => 'me.groupname } ^^ this is order_by syntax, ASC makes no sense in group_by context. Nevertheless group_by disappearing is incredibly bizarre... Show quoted text
> > This is temporary working solution: > > my $tmp_radgroup = $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> 'me.groupname', > having => 'count(check_clear.groupname) = 0', > })->get_column('me.groupname'); > $radius->resultset('RadGroup')->search({ > 'me.groupname' => { IN => $tmp_radgroup->as_query }, > })->delete(); > > provides: > DELETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupname FROM > radgroups me LEFT JOIN radgroupcheck check_clear ON > check_clear.groupname = me.groupname GROUP BY me.groupname HAVING > count(check_clear.groupname) = 0 ) ):
This is exactly what DBIC is expected to generate internally... Strange strange strange. Please attach a dump (Data::Dumper / Devel::Dwarn / whatever ) at 3 levels of depth ($Data::Dumper::Maxdepth = 3) of: $radius->resultset('RadGroup')->search(undef, { select => [ 'me.groupname' ], join => [ qw/check_clear/ ], group_by=> 'me.groupname', having => 'count(check_clear.groupname) = 0', })->_resolved_attrs; and $radius->resultset('RadGroup')->search(undef, { select => [ 'me.groupname' ], join => [ qw/check_clear/ ], group_by=> 'me.groupname', having => 'count(check_clear.groupname) = 0', })->get_column('me.groupname')->_resultset->_resolved_attrs; These *may* contain sensitive info - add them to a secret gist and email me privately if need be. *PLEASE* do not try to sanitize them - you may drop important info.
Subject: Re: [rt.cpan.org #107251] missing group by in final query
Date: Tue, 22 Sep 2015 20:43:19 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Martin Spevak <martin.spevak [...] hpe.com>
Download (untitled) / with headers
text/plain 3.2k
Ou, sorry, It has to be [ qw/me.groupname/ ] instead of { -asc => 'me.groupname' }. I have to say I tried both versions: only string and object (array). On 09/22/2015 08:39 PM, Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=107251 > > > On 09/22/2015 08:27 PM, Martin Spevak via RT wrote:
>> Tue Sep 22 14:27:19 2015: Request 107251 was acted upon. >> Transaction: Ticket created by martin.spevak@hpe.com >> Queue: DBIx-Class >> Subject: missing group by in final query >> Broken in: (no value) >> Severity: (no value) >> Owner: Nobody >> Requestors: martin.spevak@hpe.com >> Status: new >> Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=107251 > >> >> >> Hello. >> >> I found issue during resultset delete. Here is broken construction: >> >> $radius->resultset('RadGroup')->search(undef, { >> select => [ 'me.groupname' ], >> join => [ qw/check_clear/ ], >> group_by=> { -asc => 'me.groupname }, >> having => 'count(check_clear.groupname) = 0', >> })->delete(); >> #I tried also group_by => 'me.groupname'
> group_by=> { -asc => 'me.groupname } > ^^ this is order_by syntax, ASC makes no sense in group_by context. > > Nevertheless group_by disappearing is incredibly bizarre... > >
>> This is temporary working solution: >> >> my $tmp_radgroup = $radius->resultset('RadGroup')->search(undef, { >> select => [ 'me.groupname' ], >> join => [ qw/check_clear/ ], >> group_by=> 'me.groupname', >> having => 'count(check_clear.groupname) = 0', >> })->get_column('me.groupname'); >> $radius->resultset('RadGroup')->search({ >> 'me.groupname' => { IN => $tmp_radgroup->as_query }, >> })->delete(); >> >> provides: >> DELETE FROM radgroups WHERE ( groupname IN ( SELECT me.groupname FROM >> radgroups me LEFT JOIN radgroupcheck check_clear ON >> check_clear.groupname = me.groupname GROUP BY me.groupname HAVING >> count(check_clear.groupname) = 0 ) ):
> This is exactly what DBIC is expected to generate internally... Strange > strange strange. > Please attach a dump (Data::Dumper / Devel::Dwarn / whatever ) at 3 > levels of depth ($Data::Dumper::Maxdepth = 3) of: > > $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> 'me.groupname', > having => 'count(check_clear.groupname) = 0', > })->_resolved_attrs; > > and > > $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> 'me.groupname', > having => 'count(check_clear.groupname) = 0', > })->get_column('me.groupname')->_resultset->_resolved_attrs; > > These *may* contain sensitive info - add them to a secret gist and email > me privately if need be. *PLEASE* do not try to sanitize them - you may > drop important info. > >
-- *Martin (singer) Spevak* HPES Software Development Engineer HPES Network Management Solutions Location: Galvaniho 7/A, Bratislava, Slovakia Tel.: +421 2 5752 5390 Email: martin.spevak@hp.com
Subject: Re: [rt.cpan.org #107251] missing group by in final query
Date: Tue, 22 Sep 2015 21:14:25 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Martin Spevak <martin.spevak [...] hpe.com>
Download (untitled) / with headers
text/plain 2.9k
It looks, that Dumper output contains group_by. On 09/22/2015 08:39 PM, Peter Rabbitson via RT wrote: Show quoted text
> This is exactly what DBIC is expected to generate internally... Strange > strange strange. > Please attach a dump (Data::Dumper / Devel::Dwarn / whatever ) at 3 > levels of depth ($Data::Dumper::Maxdepth = 3) of: > > $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> 'me.groupname', > having => 'count(check_clear.groupname) = 0', > })->_resolved_attrs;
$VAR1 = { 'bind' => [], 'having' => 'count(check_clear.groupname) = 0', 'as' => [ 'groupname' ], '_simple_passthrough_construction' => 1, 'from' => [ { '-rsrc' => 'DBIx::Class::ResultSource::Table=HASH(0x2dd78640)', '-alias' => 'me', 'me' => 'radgroups' }, [ 'HASH(0x4be81e20)', 'HASH(0x4be88220)' ] ], 'result_source' => $VAR1->{'from'}[0]{'-rsrc'}, 'group_by' => [ 'me.groupname' ], 'alias' => 'me', 'select' => [ 'me.groupname' ] }; Show quoted text
> and > > $radius->resultset('RadGroup')->search(undef, { > select => [ 'me.groupname' ], > join => [ qw/check_clear/ ], > group_by=> 'me.groupname', > having => 'count(check_clear.groupname) = 0', > })->get_column('me.groupname')->_resultset->_resolved_attrs;
$VAR1 = { 'bind' => [], 'having' => 'count(check_clear.groupname) = 0', 'as' => [ 'groupname' ], '_simple_passthrough_construction' => 1, 'from' => [ { '-rsrc' => 'DBIx::Class::ResultSource::Table=HASH(0x2dd78640)', '-alias' => 'me', 'me' => 'radgroups' }, [ 'HASH(0x4be87178)', 'HASH(0x4be89b38)' ] ], 'result_source' => $VAR1->{'from'}[0]{'-rsrc'}, 'group_by' => [ 'me.groupname' ], 'alias' => 'me', 'select' => [ 'me.groupname' ] }; -- *Martin (singer) Spevak* HPES Software Development Engineer HPES Network Management Solutions Location: Galvaniho 7/A, Bratislava, Slovakia Tel.: +421 2 5752 5390 Email: martin.spevak@hp.com
Subject: Re: [rt.cpan.org #107251] missing group by in final query
Date: Tue, 22 Sep 2015 21:28:00 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 110b
Duh I see it. Very very stupid mistake, will look into a fix tomorrow. Thank you for the report, stay tuned!


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.