DBIx::SearchBuilder 1.51+ bug

RT Developers
1

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)

2

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: The rt-devel Archives

Best regards, Ruslan.

3

Stephen Turner 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.

Quite a while I contributed a patch for this problem but it was redrawn
because of ??
Don’t remember but could you or I look it up in svn logs, looking for
‘Joop’.
I have had in production for quite a while without side effect as far as
I could tell. Probably got removed when I updated Searchbuilder without
further thinking that my work would be undone and since we don’t rely on
CFs being ordered its probably unnoticed by my and colleague.
I use Oracle XE (is Oracle 10.2 with restrictions)

Joop

4

Stephen Turner 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.

Revision 6974 adds the patch and 7062 reverts it.

Joop

5

Stephen Turner 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.

Quite a while I contributed a patch for this problem but it was redrawn
because of ??
There are no tests in DBIx::SB’s test suite which pass with 6974 and
fail on 7062.

Don’t remember but could you or I look it up in svn logs, looking for
‘Joop’.
I have had in production for quite a while without side effect as far as
I could tell. Probably got removed when I updated Searchbuilder without
further thinking that my work would be undone and since we don’t rely on
CFs being ordered its probably unnoticed by my and colleague.
I use Oracle XE (is Oracle 10.2 with restrictions)

Joop

List info: The rt-devel Archives

Best regards, Ruslan.

6

At 6/25/2008 11:32 PM, you wrote:>On Thu, Jun 26, 2008 at 6:17 AM, Stephen Turner sturner@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