[Dbix-class] Bind parameters in select list and group by on Pg

[Peter Rabbitson]

On Fri, Oct 28, 2011 at 03:37:17AM -0700, dorian taylor wrote:
> On Fri, Oct 28, 2011 at 12:48 AM, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> > On Thu, Oct 27, 2011 at 06:11:29PM -0700, dorian taylor wrote:
> >> Hey all,
> >>
> >> I'm trying to do this:
> >>
> >> # this doesn't work with either $1 or ? formulation
> >> # my $col = [q{extract(year from (period + ?::interval))}, [ foo => '6
> >> months']];
> >>
> >> # this does when passed a bind parameter in the attributes
> >> my $col = 'extract(year from period + $1::interval)';
> >>
> >> my $rs = $db->resultset('Employee::Job')->search(
> >>     {},
> >>     {
> >>         select   => [\$col, { avg => 'wages_earned' }],
> >>         as       => ['year', 'average_wage'],
> >>         group_by => [\$col],
> >>         # use this in conjunction with the scalar ref
> >>         bind     => [[asdf => '6 months']],
> >>         order_by => 1,
> >>     }
> >> );
> >>
> >> That is, I want to aggregate by year, offset by an arbitrary month
> >> (e.g July to July). Now, the problem is, when I use a question mark
> >> placeholder and the arrayref-ref formulation, the driver appears to
> >> consider the expression in the group-by clause different from the one
> >> in the select list, yielding this error:
> >>
> >> DBI Exception: DBD::Pg::st execute failed: ERROR:  column "me.period"
> >> must appear in the GROUP BY clause or be used in an aggregate function
> >
> > Where is the DBIC_TRACE of the generated SQL that lead to this error?
> 
> array-ref-ref question mark formulation:
> 
> SELECT extract(year from (period + ?::interval)), COUNT( employee_id )
> FROM employee_job me GROUP BY extract(year from (period +
> ?::interval)) ORDER BY 1: '6 months', '6 months'
> DBI Exception: DBD::Pg::st execute failed: ERROR:  column "me.period"
> must appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: SELECT extract(year from (period + $1::interval)), COUNT( em...
>                                   ^ [for Statement "SELECT
> extract(year from (period + ?::interval)), COUNT( employee_id ) FROM
> employee_job me GROUP BY extract(year from (period + ?::interval))
> ORDER BY 1" with ParamValues: 1='6 months', 2='6 months'] at
> /usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 1078
> 
> scalar ref $1 formulation:
> 
> SELECT extract(year from period + $1::interval), COUNT( employee_id )
> FROM employee_job me GROUP BY extract(year from period + $1::interval)
> ORDER BY 1: '6 months'
> 
> (ok)
> 

Could it be that Pg is so stupid as to trip on the parenthesizing
difference of your statements:

SELECT extract(year from period + $1::interval), ...
vs
SELECT extract(year from (period + ?::interval)),

> > That... would be you using illegal syntax :)
> 
> Interesting, because the literal would be "interval '6 months'", which
> unless I'm huffing paint would translate to "interval ?". :)

Pg has a very weird way of looking at this. I do not use Pg myself, just
various memories from people with complaints *very* similar to yours.

> I understand that. I guess PG doesn't do named placeholders à la
> Oracle, does it?

Not that I know of no. And in reality positionality was never really
necessary. Let's solve your problem with positionless ?'s, there is
absolutely no reason for things not to work like that :)