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

[dorian taylor]

> 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?

-- 
Dorian Taylor
http://doriantaylor.com/