[rt-users] Sorting by priority and custom field gives number of results but not actual results

[Brian Buesker]

I have an RT 3.6.3 installation and a user reported the following 
problem. When he tried to display standard fields and custom fields in 
the results page for a search, if he tried to sort by the custom field 
value and priority, the results page did not show any results (although 
it did give the correct number of tickets matching the search). When I 
looked at the logs, I found the following SQL query had an error:

SELECT main.* FROM ( SELECT main.id FROM ((((Tickets main  LEFT JOIN 
ObjectCustomFields ObjectCustomFields_1  ON 
((ObjectCustomFields_1.ObjectId = '0')) OR (  
ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN CustomFields 
CustomFields_2  ON ( CustomFields_2.id = 
ObjectCustomFields_1.CustomField))  LEFT JOIN ObjectCustomFieldValues 
ObjectCustomFieldValues_3  ON ((ObjectCustomFieldValues_3.ObjectId = 
main.id)) AND (  ObjectCustomFieldValues_3.CustomField = 
CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0')) AND 
( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))  LEFT JOIN 
CustomFieldValues CustomFieldValues_4  ON ((CustomFieldValues_4.Name = 
ObjectCustomFieldValues_3.Content)) AND (  
CustomFieldValues_4.CustomField = 
ObjectCustomFieldValues_3.CustomField))   WHERE ((CustomFields_2.Name = 
'Release Status')) AND ((main.EffectiveId = main.id)) AND ((main.Status 
!= 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Queue = '5')AND ( 
(main.Status = 'new')OR(main.Status = 'stalled')OR(main.Status = 'open') 
) AND(main.Priority < '3')AND (  ( (ObjectCustomFieldValues_3.Content != 
'Verified')OR(ObjectCustomFieldValues_3.Content IS NULL) )  ) )   GROUP 
BY main.id   ORDER BY main.Priority ASC, main.id ASC, 
min(CustomFieldValues_4.SortOrder) ASC, 
min(ObjectCustomFieldValues_3.Content) ASC  ) distinctquery, Tickets 
main WHERE (main.id = distinctquery.id) LIMIT 50

When I tried this query directly in PostgreSQL (8.1.5), it gave the 
following error:

ERROR:  column "main.priority" must appear in the GROUP BY clause or be 
used in an aggregate function

Sure enough, if I added main.priority to the GROUP BY clause, the query 
worked. We have version 1.43 of DBIx::SearchBuilder. Is this something 
that might be fixed in a newer version of DBIx::SearchBuilder (I didn't 
see anything obvious from the changelog), or are there any other 
solutions to this problem?

Thanks,
Brian