[Dbix-class] Count/pager and resultset differences when using a custom function

[Jan Grmela]

Hello,
we use DBIx::Class 0.08196 along with Catalyst, Template Toolkit, etc
in our web project. The underlying DB is Postgres.

Recently, we discovered a strange behavior when using complex select
clauses with custom functions. The resultset items array is correct,
the produced query is exactly as we wanted:
Perl: http://pastebin.com/dLL9UNbF
SQL: http://pastebin.com/uq7xmUhQ

However, when we try to use a pager or get the items count of this
resultset, the query is wrong and doesn't reflect the custom function
we're passing:
SQL: http://pastebin.com/ph17zjjq

Yep, I know there are a "group_by" or "distinct => 1" params for the
search method -- we tried that earlier and we couldn't make it work as
we wanted because of the specifics of the Postgres SQL engine. The
query we're currently getting is exactly what we want from the SQL
Query Builder.

Is there something I could do to
a) make the pager use the select list and the custom function
-- or --
b) change the initial query some way to select exactly the same
results using parameters that the COUNT query will understand
?

Thank you and happy Easter ;)

-- 
Jan Grmela
jan.grmela at imakers.cz
+420 608 110 686

iMakers, s.r.o.
Absolonova 725/81, Brno, 62400, Czech Republic

ICO/Id: 29228875
DIC/VAT No: CZ29228875