Skip Menu |
 

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

Report information
The Basics
Id: 18436
Status: resolved
Worked: 10 min
Priority: 0/
Queue: DBIx-SearchBuilder

People
Owner: RUZ [...] cpan.org
Requestors: joby [...] u.washington.edu
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: 1.40
Fixed in: 1.55



Subject: ::Handle::Pg->DistinctQuery Doesn't work right
Download (untitled) / with headers
text/plain 1.5k
Using Group By to do the distinct query is a good try but this fails if any of the Order By Criteria are in the main table because Postgres requires that if there is a group by criteria it must include all of the Order By fields, unless they are an aggregate function. The following example fails because main.starts is not in an aggregate function or in the Group By clause. If main.starts is added to the Group By clause or wrapped with an aggregate function it works fine: SELECT main.* FROM ( SELECT main.id FROM (((Tickets main LEFT JOIN CustomFields CustomFields_1 ON ( CustomFields_1.Name = 'Severity')) LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON ( ObjectCustomFieldValues_2.CustomField = CustomFields_1.id) AND ( (ObjectCustomFieldValues_2.Disabled = '0')) AND ( (ObjectCustomFieldValues_2.ObjectId = main.id)) AND ( (ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket'))) LEFT JOIN CustomFieldValues CustomFieldValues_3 ON ( CustomFieldValues_3.CustomField = ObjectCustomFieldValues_2.CustomField) AND ( (CustomFieldValues_3.Name = ObjectCustomFieldValues_2.Content))) WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Status = 'open')AND(main.Queue = '10')) GROUP BY main.id ORDER BY min(CustomFieldValues_3.SortOrder) ASC, min(ObjectCustomFieldValues_2.SortOrder) ASC, min(ObjectCustomFieldValues_2.Content) ASC, main.Starts ASC ) distinctquery, Tickets main WHERE (main.id = distinctquery.id) LIMIT 50 This is with RT-3.4.5, the BP code for the University of Washington, and a bit more extra code on our part.
Download (untitled) / with headers
text/plain 1.9k
Hello, There were several changes in that code since 2006 and the last one in version 1.55 where we fixed another bug and switched to different approach again. I believe that 1.55 handles issue quotted below just fine. I'm resolving this ticket. On Wed Mar 29 14:49:05 2006, guest wrote: Show quoted text
> Using Group By to do the distinct query is a good try but this fails if > any of the Order By Criteria are in the main table because Postgres > requires that if there is a group by criteria it must include all of the > Order By fields, unless they are an aggregate function. > > The following example fails because main.starts is not in an
aggregate Show quoted text
> function or in the Group By clause. If main.starts is added to the > Group By clause or wrapped with an aggregate function it works fine: > > SELECT main.* FROM ( SELECT main.id FROM (((Tickets main LEFT
JOIN Show quoted text
> CustomFields CustomFields_1 ON ( CustomFields_1.Name =
'Severity')) Show quoted text
> LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON ( > ObjectCustomFieldValues_2.CustomField = CustomFields_1.id) AND ( > (ObjectCustomFieldValues_2.Disabled = '0')) AND ( > (ObjectCustomFieldValues_2.ObjectId = main.id)) AND ( > (ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket'))) LEFT JOIN > CustomFieldValues CustomFieldValues_3 ON ( > CustomFieldValues_3.CustomField =
ObjectCustomFieldValues_2.CustomField) Show quoted text
> AND ( (CustomFieldValues_3.Name =
ObjectCustomFieldValues_2.Content))) Show quoted text
> WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) > AND ((main.Type = 'ticket')) AND ((main.Status =
'open')AND(main.Queue = Show quoted text
> '10')) GROUP BY main.id ORDER BY
min(CustomFieldValues_3.SortOrder) Show quoted text
> ASC, min(ObjectCustomFieldValues_2.SortOrder) ASC, > min(ObjectCustomFieldValues_2.Content) ASC, main.Starts ASC ) > distinctquery, Tickets main WHERE (main.id = distinctquery.id) LIMIT
50 Show quoted text
> > > This is with RT-3.4.5, the BP code for the University of Washington,
and Show quoted text
> a bit more extra code on our part.
-- Best regards, Ruslan.


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.