[Dbix-class] Bind parameters in select list and group by on Pg
dorian.taylor.lists at gmail.com
Fri Oct 28 13:07:32 GMT 2011
> 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), ...
> 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
SELECT extract(year from period + ?::interval), COUNT(employee_id)
GROUP BY extract(year from period + ?::interval);
SELECT extract(year from period + $1::interval), COUNT(employee_id)
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
> 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
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?
More information about the DBIx-Class