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

[Paul Newell]

The duplication of the column in the select, though it appears to be a bug,
shouldn't really be a problem as you are not adding any new entities that
can be identified as "quantity".

There is a problem in the general statement generated as, at least in the
versions of SQL that I remember, it makes no sense to select distinct
values of columns in a set grouped by another column. That is, in order to
be meaningful in the traditional sense the only columns other than the
group-by columns you can include would be sum(column), avg (column), etc.

Given the error that mysql provides it would appear that it is okay with
this, so it has some other interpretation of the statement. I know that SQL
Server and Sybase had something like this but it never made much sense to
me and wasn't a part of the ANSI standard of the time.

The only way to correct this that I can think of is to remove all the
columns from the result set and perhaps add the constant 1 or even sum(1)
which would give you your result without the count (unless the ->count
gives you the number of distinct quantities in which case it should still
work).


On 10 October 2017 at 15:12, Vladimir Melnik <v.melnik at uplink.ua> wrote:

> Thank you, Gerhard!
>
> It was almost the first thing I tried, but it hadn't help. :-)
>
> On Tue, Oct 10, 2017 at 04:26:21PM +0200, Gerhard Jungwirth wrote:
> > just guessing, did you try
> >
> > group_by => [ 'me.quantity' ]
> >
> >
> > On 2017-10-10 13:20, Vladimir Melnik 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!
> > >
> >
> >
> > _______________________________________________
> > 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
>
> --
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20171010/dfabe825/attachment-0001.htm>