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

Report information
The Basics
Id:
107251
Status:
open
Priority:
Low/Low
Queue:

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

BugTracker
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>
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>
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>
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>
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>
Duh I see it. Very very stupid mistake, will look into a fix tomorrow. Thank you for the report, stay tuned!


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

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