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:
:_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://svn.bestpractical.com/cgi-bin/index.cgi/bps/revision/?rev=17942On Fri, Jan 23, 2009 at 6:56 PM, Kenneth Marshall ktm@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::_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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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.