Skip Menu |
 

This queue is for tickets about the DBIx-SearchBuilder CPAN distribution.

Report information
The Basics
Id: 42836
Status: resolved
Priority: 0/
Queue: DBIx-SearchBuilder

People
Owner: RUZ [...] cpan.org
Requestors: ktm [...] rice.edu
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: PostgreSQL does not GROUP BY the elements in the SELECT
Date: Tue, 27 Jan 2009 12:00:06 -0600
To: bug-DBIx-SearchBuilder [...] rt.cpan.org
From: Kenneth Marshall <ktm [...] rice.edu>
Download (untitled) / with headers
text/plain 2.4k
Dear DBIx-SearchBuilder developers, I am reporting a bug with the generation of the GROUP BY query using the PostgreSQL DB backend. I am using the latest release 1.54 with perl 5.8.8 on a RHEL4 machine against a PostgreSQL 8.3.3 databse. The bad query is generated from the RT 3.8.2 Tools/Reports/ResolvedByDates.html page. Here is the error logged: Jan 27 11:34:19 rt1 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 27 11:34:19 rt1 RT: RT::Handle=HASH(0xa8969b4) 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-27 23:34:18' 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(0xa8969b4)', '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(0xb21e7f4)') called at /usr/site/rt-3.8/DEV/bin/../lib/RT/Tickets_Overlay.pm line 2672 RT::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb21e7f... As you can see, the SELECT is against id and Users_2.Name but the GROUP BY is against Users_1.Name. If I change Users_1.Name to Users_2.Name and re-run the query manually, I get the correct results. The problem is akin to asking for a list of people and the credit cards they have and then trying to group this data by their favorite candy. How is the database to know that Users_1.Name is the same as Users_2.Name with respect to the GROUP BY? I do not understand the inputs and outputs of the module well enough to suggest a patch. If there were a set of equivalences that could be used to convert the wrong Users_1.Name to the correct one, it would work. As near as I can tell, this problem has existed for some time but I have not checked to see how many revisions back it goes. Please let me know if there is anything I can do to help resolve this problem. Cheers, Ken Marshall


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.