> 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/ _______________________________________________ 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/[email protected]
