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

[Peter Rabbitson]

On Fri, Oct 28, 2011 at 06:07:32AM -0700, dorian taylor wrote:
> > 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)),
> 
> No; I tried different permutations. That difference is just a relic.
> The parentheses are superfluous.
> 
> > 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'm suspicious that the planner evaluates expressions before it binds
> variables, so:
> 
> SELECT extract(year from period + ?::interval), COUNT(employee_id)
> FROM employee_job
> GROUP BY extract(year from period + ?::interval);
> 
> would yield:
> 
> SELECT extract(year from period + $1::interval), COUNT(employee_id)
> FROM employee_job
> GROUP BY extract(year from period + $2::interval);
> 
> which would naturally be different expressions, whereas $1::interval
> in the GROUP BY expression would be identical to the one in the select
> list.
> 
> > 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 :)
> 
> I agree. It's kind of a dodgy constraint.
> 
> For what it's worth it behaves the same sans DBIx::Class, which
> implies that the behaviour is either in DBI, DBD::Pg, libpq or the
> server itself.
> 
> So the problem, reformulated, is:
> 
> * I have a column called 'period' that has dates justified to month-ends
> * I want to select aggregates over other columns in the table (plus
> other joined tables)
> * I want to extract the year from the period plus N months which would
> enable me to aggregate over, say, July to July.
> * The SQL (Pg) expression for that is extract(year from period + ?::interval)
> * There may be other equivalent expressions
> * The query fails with question-mark bind parameters, but succeeds
> when the ? in the above expression in both the select list and
> group-by clause is replaced by $1
> * This makes it difficult to coexist with DBIx::Class
> 
> What do you suppose I should try next?
> 

Perhaps this:

http://search.cpan.org/~turnstep/DBD-Pg-2.18.1/Pg.pm#pg_server_prepare_%28integer%29