Bug #107251 for DBIx-Class: missing group by in final query

Tue Sep 22 14:27:19 2015 martin.spevak [...] hpe.com - Ticket created

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>

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

Tue Sep 22 14:39:21 2015 ribasushi [...] leporine.io - Correspondence added

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>

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.

Tue Sep 22 14:39:22 2015 The RT System itself - Status changed from 'new' to 'open'

Tue Sep 22 14:43:39 2015 martin.spevak [...] hpe.com - Correspondence added

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>

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

Tue Sep 22 15:14:45 2015 martin.spevak [...] hpe.com - Correspondence added

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>

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

Tue Sep 22 15:28:18 2015 ribasushi [...] leporine.io - Correspondence added

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>

Duh I see it. Very very stupid mistake, will look into a fix tomorrow. Thank you for the report, stay tuned!

Sun Jan 28 12:26:38 2018 ribasushi [...] leporine.io - Dependency by ticket #124246 added