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

[Stephen Turner]

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)

CF: Software 12
CF: Model 109
CF: Jack Number 111
CF: Method 112
CF: Category 7
CF: OS 8
CF: Support Level 10
CF: Serial Number (CPU) 11
CF: MIT Tag 13
CF: MAC Address 14
CF: Warranty Expiration Date 15

Obviously the order is wrong. I'm wondering why the generated sql has 
a query within a query - why not just this:

SELECT main.* 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

Thanks,
Steve


Stephen Turner
Senior Programmer/Analyst - SAIS
MIT Information Services and Technology (IS&T)