[Dbix-class] A count() after a grouping select() produces a "Duplicate column" error

[Vladimir Melnik]

Thank you, Robert!

The ResultSet is a plain one, it's being created by calling
resultset('ProvisioningObligation'), no modifications are being
performed. The "quantity" field had been choosen only as an example, but
ANY field mentioned in the "group_by" attribute's value is being
duplicated.

On Tue, Oct 10, 2017 at 09:16:59AM -0500, Robert Stone wrote:
> Greetings,
> 
> I'm happy to take a stab at it!  If anyone sees anything I'm missing please
> feel free to add your thoughts.
> 
> Looking at the generated SQL:
> 
> SELECT
>   COUNT( * )
>   FROM (
>       SELECT
>         me.id
>         ,me.valid_from
>         ,me.valid_till
>         ,me.removed
>         ,me.provisioning_agreement_id
>         ,me.resource_piece_id
>         ,me.service_type_id
>         ,me.service_level_id
> *        ,me.quantity*
> *        ,me.quantity*
>         FROM provisioning_obligation me
>         GROUP BY quantity
>   ) me
> 
> We can see that the quantity column is appearing twice (which is what is
> generating that Duplicate column name, because the derived table has the
> same column twice so it can't be "fed into" the SELECT COUNT(*) ).  Is it
> possible you copy pasted the quantity definition in the result source an
> extra time?  Alternatively, how are you generating the $resultset you are
> using, is it possible you are tacking on an extra quantity then, or are you
> querying the provisioning_obligation result source directly?
> 
> More importantly though, I'm curious as to what information you are
> actually trying to get.  You want a distinct count of quantities from the
> provisioning_obligation table?  I'm not sure the utility of that
> information, but a better way to answer that question would be to use a
> SELECT COUNT( DISTINCT quantity ), there is a great example in the docs at:
> 
> http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#SELECT_COUNT(DISTINCT_colname)
> 
> Hopefully you find this helpful.
> 
> Best Regards,
> Robert Stone
> 
> 
> On Tue, Oct 10, 2017 at 6:20 AM, Vladimir Melnik <v.melnik at uplink.ua> wrote:
> 
> > Dear colleagues,
> >
> > Here's a little snippet of the code:
> >         $resultset
> >             ->search(undef, { group_by => [ qw(quantity) ] })
> >             ->count;
> >
> > It produces the following exception:
> > DBI Exception: DBD::mysql::st execute failed: Duplicate column name
> > 'quantity' [for Statement "SELECT COUNT( * ) FROM (SELECT me.id,
> > me.valid_from, me.valid_till, me.removed, me.provisioning_agreement_id,
> > me.resource_piece_id, me.service_type_id, me.service_level_id, me.quantity,
> > me.quantity FROM provisioning_obligation me GROUP BY quantity) me"] at
> > /home/mmkeeper/perl5/perlbrew/perls/perl-5.24.0/lib/site_
> > perl/5.24.0/DBIx/Class/Schema.pm line 1118.
> >
> > What is the best way to avoid that?
> >
> > Thanks in advance!
> >
> > --
> > V.Melnik
> >
> > _______________________________________________
> > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> > Searchable Archive: http://www.grokbase.com/group/
> > dbix-class at lists.scsys.co.uk
> >

> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk


-- 
V.Melnik