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

Owner: RUZ [...]
Requestors: ktm [...]

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 [...]
From: Kenneth Marshall <ktm [...]>
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 "" 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/ Jan 27 11:34:19 rt1 RT: RT::Handle=HASH(0xa8969b4) couldn't execute the query 'SELECT COUNT( AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( = main.Owner ) LEFT JOIN Users Users_1 ON ( = 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 = GROUP BY Users_1.Name ' at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/ line 518 DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xa8969b4)', 'SELECT COUNT( AS id, Users_2.Name AS col1 FROM Ticket...') called at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/ line 238 DBIx::SearchBuilder::_DoSearch('RT::Report::Tickets=HASH(0xb21e7f4)') called at /usr/site/rt-3.8/DEV/bin/../lib/RT/ 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 infrastructure.

Please report any issues with to