Sortorder of customfields

Hello,

I’m, actually a client of ours, having problems with the sortorder of
the customfields displayed on Ticket display and Asset Display.html
In Configuration the order is correctly displayed and in the database
the order is also correct.
At the moment our client is entering quite a bit of data, assets, and
last week they had the correct order but apparently they have added a
couple of types and maybe reordered a couple of customfields and now
they are seeing that the order is way off.
I can reproduce this weird behaviour by moving CF1 one entry down and
then back up, to the original position, in Configuration and when I go
to an asset with these customfields I can see that the order is
different then when I started this. In one particular case CF was
displayed LAST instead of FIRST.

After a bit of searching I found the query which is responsible for
delivering the order to the element which displays it and there are
several problems with that query.
BTW this is RT-3.4.5, AT-1.2.3 using Oracle XE and DBIx::SearchBuilder v1.40,
complete config is available on request.
This is the query that is responsible for the display order:

SELECT main.*
FROM (SELECT main.ID
FROM customfields main, objectcustomfields objectcustomfields_1
WHERE ( (objectcustomfields_1.objectid = ‘3’)
OR (objectcustomfields_1.objectid = ‘0’)
)
AND ((main.disabled = ‘0’))
AND ((main.lookuptype =
‘RTx::AssetTracker::Type-RTx::AssetTracker::Asset’
)
)
AND ((main.ID = objectcustomfields_1.customfield))
GROUP BY main.ID
ORDER BY MIN (objectcustomfields_1.objectid) ASC,
MIN (objectcustomfields_1.sortorder) ASC) distinctquery,
customfields main
WHERE (main.ID = distinctquery.ID)

This will give you the customfields belonging to asset_type 3 in the
order as they are to be displayed, not.
Problem is the GROUP BY together with the MIN, removing those statements
get the correct sort order list BUT my dba tells me that there is no guarantee
that this will give you always the correct order. If the optimizer
decides to use the second table as leading then the order will change.
The solution apparently is the have an ORDER BY on the outer SELECT like
this:
SELECT main.*
FROM (SELECT main.ID
FROM customfields main, objectcustomfields objectcustomfields_1
WHERE ( (objectcustomfields_1.objectid = ‘3’)
OR (objectcustomfields_1.objectid = ‘0’)
)
AND ((main.disabled = ‘0’))
AND ((main.lookuptype =
‘RTx::AssetTracker::Type-RTx::AssetTracker::Asset’
)
)
AND ((main.ID = objectcustomfields_1.customfield))
ORDER BY (objectcustomfields_1.objectid) ASC,
(objectcustomfields_1.sortorder) ASC) distinctquery,
customfields main
WHERE (main.ID = distinctquery.ID)
order by (select distinct objectcustomfields_1.sortorder
FROM objectcustomfields objectcustomfields_1
WHERE ( (objectcustomfields_1.objectid = ‘3’)
OR (objectcustomfields_1.objectid = ‘0’)
)
AND ((main.disabled = ‘0’))
AND ((main.lookuptype =
‘RTx::AssetTracker::Type-RTx::AssetTracker::Asset’
)
)
AND ((main.ID = objectcustomfields_1.customfield)))

This doesn’t look right and can be simplified but I doubt that in itself
would be simple. This query is build in
DBIx/SearchBuilder/Handle/Oracle.pm, sub DistinctQuery()

Is there anyone who knows why that function is built that way because
I’m going to try to fix this but if there is some logic in there that is
not directly clear from it then it should be commented so that feature
problems can be solved more rapidly.

Thanks for any help anyone can offer on this,

Joop

Joop van de Wege JoopvandeWege@mococo.nl

Hello All,

I have been reading up on SELECTs and ORDER BY and GROUP BY and having a
chat with my DBA and we have come to the conclusion that the following
piece of code from DBIx/SearchBuilder/Handle/Oracle.pm is plain wrong.
sub DistinctQuery {
my $self = shift;
my $statementref = shift;
my $sb = shift;
my $table = $sb->Table;

# Wrapp select query in a subselect as Oracle doesn't allow
# DISTINCT against CLOB/BLOB column types.
if ($sb->_OrderClause =~ /(?<!main)\./) {
    # If we are ordering by something not in 'main', we need to GROUP
    # BY and adjust the ORDER_BY accordingly
    local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD => 'id'}];
    local $sb->{order_by} = [map {($_->{ALIAS} and $_->{ALIAS} ne "main") ? {%{$_}, FIELD => "min(".$_->{FIELD}.")"}: $_} @{$sb->{order_by}}];
    my $group = $sb->_GroupClause;
    my $order = $sb->_OrderClause;
    $$statementref = "SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)";
} else {
    $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";
    $$statementref .= $sb->_GroupClause;
    $$statementref .= $sb->_OrderClause;
}

}

We have no clue whatsoever where that comment about orderby not being
the main table is going to need a groupby.
This is atleast not true for Oracle 9i, never has been and can be
removed.
This solves the ordering problem with customfields with one side note
that to be 100% sure that you get the desired order you would need to
have the subselect in the order by clause, as explained in the previous
post.

By looking at the svn repository I can see that 4254 is the last change
and it was introduced then. Looks like it is verbatim copied from Pg.pm
where this might be true.

Joop

Hello All,

I have been reading up on SELECTs and ORDER BY and GROUP BY and having a
chat with my DBA and we have come to the conclusion that the following
piece of code from DBIx/SearchBuilder/Handle/Oracle.pm is plain wrong.

Joop,

I have had the same problem with the sort order of CFs for
a long time, but we use MySQL, so I don’t think it is an
Oracle specific problem.

-Todd`

Joop van de Wege wrote:

Hello All,

I have been reading up on SELECTs and ORDER BY and GROUP BY and having a
chat with my DBA and we have come to the conclusion that the following
piece of code from DBIx/SearchBuilder/Handle/Oracle.pm is plain wrong.
sub DistinctQuery {

}

We have no clue whatsoever where that comment about orderby not being
the main table is going to need a groupby.
This is atleast not true for Oracle 9i, never has been and can be
removed.

I recall that this change was made at the request of a customer who had
an oracle sorting issue. I don’t doubt your analysis at all. Alex, do
you remember the issue we were looking to solve with it? Joop, do you
have a proposed patch?

This solves the ordering problem with customfields with one side note
that to be 100% sure that you get the desired order you would need to
have the subselect in the order by clause, as explained in the previous
post.

By looking at the svn repository I can see that 4254 is the last change
and it was introduced then. Looks like it is verbatim copied from Pg.pm
where this might be true.

I’m pretty sure it is, there.

signature.asc (191 Bytes)

By looking at the svn repository I can see that 4254 is the last change
and it was introduced then. Looks like it is verbatim copied from Pg.pm
where this might be true.

I’m pretty sure it is, there.

FWIW, I’ll bet that the GROUP BY ORDER BY addition in any Postgres
related files was added because of the change in 8.x. Hash sorting
in that release meant that GROUP BY no longer entailed a return order
to results. Lots of people had relied on the old rule of thumb that
you could get away without the additional ORDER BY when you did GROUP
BY, because the old implementation required a qsort on the groups
before they could be grouped; so everything came back in the order
you expected. Once hash sorting was implemented, that assumption was
no longer true. (To be fair, Tom Lane had been warning everyone
since I started using Postgres that he was planning for that to
break. It just took him some time to get around to it.)

A

Andrew Sullivan | ajs@crankycanuck.ca
It is above all style through which power defers to reason.
–J. Robert Oppenheimer

Joop van de Wege wrote:

Hello All,

We have no clue whatsoever where that comment about orderby not being
the main table is going to need a groupby.
This is atleast not true for Oracle 9i, never has been and can be
removed.

I recall that this change was made at the request of a customer who had
an oracle sorting issue. I don’t doubt your analysis at all. Alex, do
you remember the issue we were looking to solve with it? Joop, do you
have a proposed patch?
Patch attached, might need some cleaning up.

This solves the ordering problem with customfields with one side note
that to be 100% sure that you get the desired order you would need to
have the subselect in the order by clause, as explained in the previous
post.
Haven’t done this part (yet). I’m not sure it is needed but I’m having
no time to look at it now

By looking at the svn repository I can see that 4254 is the last change
and it was introduced then. Looks like it is verbatim copied from Pg.pm
where this might be true.

I’m pretty sure it is, there.
Have been reading the Pg documentation but couldn’t find any reference
to it but I’ll trust you on this :wink:

Joop

Joop van de Wege JoopvandeWege@mococo.nl

Oracle.diff (1.65 KB)