[rt-users] Problem with Reports in RT 3.8.2 with PostgreSQL backend

[Ruslan Zakirov]

http://svn.bestpractical.com/cgi-bin/index.cgi/bps/revision/?rev=17942

On Fri, Jan 23, 2009 at 6:56 PM, Kenneth Marshall <ktm at rice.edu> wrote:
> Dear RT Users,
>
> We are testing RT 3.8.2 using a PostgreSQL 8.3 backend and
> the Tools/Reports does not work properly. The problem is that
> the GROUP BY does not use one or more of the SELECT columns.
> Here is the error in the logs:
>
> Jan 23 08:41:31 rtx RT: DBD::Pg::st execute failed:
>
> ERROR:  column "users_2.name" must appear in the GROUP BY clause or be used in an aggregate function (/usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
> Jan 23 08:41:31 rt1 RT: RT::Handle=HASH(0xa88faec) couldn't execute the query
>
> 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )  WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '27' AND main.Resolved < '2009-01-23 20:41:30' AND main.Resolved > '2009-01-01 12:00:00') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY Users_1.Name '
>
> at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518       DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xa88faec)', 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Ticket...') called at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder.pm line 238   DBIx::SearchBuilder::_DoSearch('RT::Report::Tickets=HASH(0xb01e234)') called at /usr/site/rt-3.8/DEV/bin/../lib/RT/Tickets_Overlay.pm line 2672   RT::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb01e23...
>
> If the column used to GROUP BY is changed to the selected column
> Users_2.Name instead of Users_1.Name the query works as expected.
> It looks like it may be a problem with DBIx::SearchBuilder, but I
> wanted to check here before posting a CPAN bug report. Any help
> would be appreciated.
>
> Cheers,
> Ken Marshall
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.