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

[Ruslan Zakirov]

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.

>
> 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:
As far as I know not all versions support "GROUP BY main.id" with
"SELECT main.* ".

>
> 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)
>
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>



-- 
Best regards, Ruslan.