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 _______________________________________________ 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]
