[Rt-devel] DBIx::SearchBuilder 1.51+ bug

[Stephen Turner]

At 6/25/2008 11:32 PM, you wrote:
>On Thu, Jun 26, 2008 at 6:17 AM, Stephen Turner <sturner at mit.edu> wrote:
> > We discovered a bug in (I think) DBIx::SearchBuilder, in the
> > Oracle-specific part of the package - the DistinctQuery method in
> > Handle/Oracle.pm. The problem is that results from some queries are
> > not sorted correctly. Where we have seen this in RT (3.6.5) is in the
> > order of custom fields on ticket pages (Display, Basics etc) - the
> > order of CFs on the screen does not match the sort order specified
> > for the fields.
> >
> > We've seen this problem using SB 1.51 and 1.53 against an Oracle 
> 9.2 database.
> >
> > Here's a fragment of a simple Perl script that shows the problem. It
> > queries a ticket's custom fields and lists the field names along with
> > the sort order specified for the queue:
> >
> > my $cfs = $tkt->CustomFields;
> >
> > print $cfs->BuildSelectQuery(), "\n\n";
> >
> > while (my $cf = $cfs->Next) {
> >     my $ocf =  RT::ObjectCustomField->new($CurrentUser);
> >     $ocf->LoadByCols( ObjectId  => $tkt->Queue,
> >                       CustomField => $cf->id);
> >     print "CF: ".$cf->Name. " " . $ocf->SortOrder. "\n";
> > }
> >
> >
> > This produces the following output:
> >
> > SELECT main.* FROM ( SELECT main.id FROM CustomFields main JOIN
> > ObjectCustomFields ObjectCustomFields_1  ON (
> > ObjectCustomFields_1.CustomField = main.id )  WHERE
> > (ObjectCustomFields_1.ObjectId = '41' OR
> > ObjectCustomFields_1.ObjectId = '0') AND (main.LookupType =
> > 'RT::Queue-RT::Ticket')   GROUP BY main.id   ORDER BY
> > min(ObjectCustomFields_1.ObjectId) ASC,
> > min(ObjectCustomFields_1.SortOrder) ASC  ) distinctquery,
> > CustomFields main WHERE (main.id = distinctquery.id)



>Have you tested this query from oracle shell? I ask as we have tests
>in SB for this issue and I tested it against Oracle 10.

Yes, same result (wrong order in returned rows). Oracle version is 9.2.0

Steve