Sort Order of Custom Fields in rt 3.6.1

Hello,

we have used RT 3.6.0 so I upgraded to RT 3.6.1 today. I had to
install the CPAN Module UNIVERSAL::require to get RT 3.6.1 to run.

Since I installed 3.6.1 our custom order of our Custom Fields
(Configuration -> Queues -> -> Ticket Custom Fields) is
simply ignored, when one views or edits a Ticket.

So i came back to 3.6.0 and paradoxically the problem still exists!

Any ideas how to get this problem fixed?

thanks in advance,
Sebastian Schwerdh�fer

Hello,

we have used RT 3.6.0 so I upgraded to RT 3.6.1 today. I had to
install the CPAN Module UNIVERSAL::require to get RT 3.6.1 to run.

Since I installed 3.6.1 our custom order of our Custom Fields
(Configuration → Queues → → Ticket Custom Fields) is
simply ignored, when one views or edits a Ticket.

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Jesse Vincent wrote:

Since I installed 3.6.1 our custom order of our Custom Fields
(Configuration → Queues → → Ticket Custom Fields) is
simply ignored, when one views or edits a Ticket.

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

PS: Excuse me for beeing late, i overlooked your mail. So my boss had to
notify me of your mail! Shame on myself…

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

PS: Excuse me for beeing late, i overlooked your mail. So my boss had to
notify me of your mail! Shame on myself…

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

-jesse

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).

/Ole Craig
Security Engineer
Team lead, customer support

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).
CF sorting in MySQL should work.


/Ole Craig
Security Engineer
Team lead, customer support

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).
CF sorting in MySQL should work.

Guess I’m just lucky… I’ve got a select-one-value Ticket CF named
“Severity” which currently has the following possible name values:

    '1 - CRITICAL'
    '2 - Major'
    '3 - Minor'
    '4 - Informational'

(sort ordering is 1234 and the value name begins with the appropriate
digit as well.)

If I sort my “N” highest priority tickets at-a-glance page on that CF
(and that CF alone, all other sort bars are set to “none”) I get a
random list. At the moment, it looks like this:

3 - Minor
3 - Minor
1 - CRITICAL
2 - Major
2 - Major
4 - Informational
2 - Major
3 - Minor
[…]

No other order is apparent in the ticket list.

Thoughts?

/Ole Craig
Security Engineer
Team lead, customer support

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .

sorry, I didn’t test now I see that it’s broken on mysql too :(On 10/5/06, Ole Craig ocraig@stillsecure.com wrote:

On Thu, 2006-10-05 at 01:40 +0400, Ruslan Zakirov wrote:

On 10/4/06, Ole Craig ocraig@stillsecure.com wrote:

On Thu, 2006-09-28 at 14:46 -0400, Jesse Vincent wrote:

So i came back to 3.6.0 and paradoxically the problem still exists!

I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).
CF sorting in MySQL should work.

Guess I’m just lucky… I’ve got a select-one-value Ticket CF named
“Severity” which currently has the following possible name values:

    '1 - CRITICAL'
    '2 - Major'
    '3 - Minor'
    '4 - Informational'

(sort ordering is 1234 and the value name begins with the appropriate
digit as well.)

If I sort my “N” highest priority tickets at-a-glance page on that CF
(and that CF alone, all other sort bars are set to “none”) I get a
random list. At the moment, it looks like this:

3 - Minor
3 - Minor
1 - CRITICAL
2 - Major
2 - Major
4 - Informational
2 - Major
3 - Minor
[…]

No other order is apparent in the ticket list.

Thoughts?


/Ole Craig
Security Engineer
Team lead, customer support

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .

Best regards, Ruslan.

Yes, and it is broken in 3.6.1 too :frowning:

Torsten2006/10/5, Ruslan Zakirov ruslan.zakirov@gmail.com:

sorry, I didn’t test now I see that it’s broken on mysql too :frowning:

On 10/5/06, Ole Craig ocraig@stillsecure.com wrote:

On Thu, 2006-10-05 at 01:40 +0400, Ruslan Zakirov wrote:

On 10/4/06, Ole Craig ocraig@stillsecure.com wrote:

On Thu, 2006-09-28 at 14:46 -0400, Jesse Vincent wrote:

So i came back to 3.6.0 and paradoxically the problem still
exists!

I’m betting that this is due to an upgraded
DBIx::SearchBuilder. Just
out of curiosity, what database are you on?

Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going
to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).
CF sorting in MySQL should work.

Guess I’m just lucky… I’ve got a select-one-value Ticket CF named
“Severity” which currently has the following possible name values:

    '1 - CRITICAL'
    '2 - Major'
    '3 - Minor'
    '4 - Informational'

(sort ordering is 1234 and the value name begins with the appropriate
digit as well.)

If I sort my “N” highest priority tickets at-a-glance page on that CF
(and that CF alone, all other sort bars are set to “none”) I get a
random list. At the moment, it looks like this:

3 - Minor
3 - Minor
1 - CRITICAL
2 - Major
2 - Major
4 - Informational
2 - Major
3 - Minor
[…]

No other order is apparent in the ticket list.

Thoughts?


/Ole Craig
Security Engineer
Team lead, customer support

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .


Best regards, Ruslan.


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

MFG

Torsten Brumm

http://www.torsten-brumm.de

Hello Ole,> On Thu, 2006-09-28 at 14:46 -0400, Jesse Vincent wrote:

So i came back to 3.6.0 and paradoxically the problem still exists!
I’m betting that this is due to an upgraded DBIx::SearchBuilder. Just
out of curiosity, what database are you on?
Our RT Installation uses PostgreSQL 8.0.3.

[…]

Ok. Yes, this is a known bug (and one that it turns out is going to be a
real pain to fix)

Jesse -
Is that the case for all combinations? I’m running into a
similar CF sort problem with MySQL 4.1 and 3.6.0.
(DBIx-SearchBuilder-1.43).

I wrote the following to rt-devel last april:

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;

 # Wrap select query in a sub select 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 at least 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 sub select 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

I have gone ahead and modified SearchBuilder todo what I described and
the customer so far is very pleased :wink:

Jesse did indicate that Alex made the change in the Progress part of SB
and it looked like it was copied verbatim to the other database drivers,
at least it was copied to Oracle.pm, don’t know about MySQL.
The relevant SVN change is: 4254

Hope this helps for you all,

Joop

Guess I’m just lucky… I’ve got a select-one-value Ticket CF named
“Severity” which currently has the following possible name values:

    '1 - CRITICAL'
    '2 - Major'
    '3 - Minor'
    '4 - Informational'

(sort ordering is 1234 and the value name begins with the appropriate
digit as well.)

If I sort my “N” highest priority tickets at-a-glance page on that CF
(and that CF alone, all other sort bars are set to “none”) I get a
random list. At the moment, it looks like this:

3 - Minor
3 - Minor
1 - CRITICAL
2 - Major
2 - Major
4 - Informational
2 - Major
3 - Minor
[…]

No other order is apparent in the ticket list.

So… after digging through the changelogs for both RT 3.6.3-RCx and
DBIx::Searchbuilder-1.45 and seeing no mention of this bug, I got
frustrated and decided to dig a little. I’ve been playing with queries
all night, and it looks to me like this bug is exercised when you have
more than one custom field defined for your queue; RT (or SB) passes a
query to mysql that is not sufficiently constrained, so you get ordering
by the SortOrder of all custom fields rather than just the one you
specified.

I stripped an RT search down to just a few fields and sorted on my
custom “severity” field while running mysql with general query logging,
and the query that came through looks like the attached sql-1.txt. When
I ran that by hand it returned the seemingly random order I was seeing
in RT (result-1.txt) but when I added an extra SELECT field
(CustomFieldValues_4.Name) I noticed that the values returned for
CustomFieldValues.Name were from every possible custom field defined for
the queue, not just the Severity CF. (sql-2.txt, result-2.txt).

So I added an extra JOIN and an extra WHERE clause to the original
query, to link CustomFieldValues_4 to the severity CF, and lo and behold
the results came out in correct order! (sql-3.txt, result-3.txt) Lastly,
for the sake of completeness I added the extra JOIN and WHERE clauses to
the second query with the extra SELECT, and this time it displayed only
names from the Severity CF, instead of all possible CFs defined on my
support queue. (sql-4.txt, result-4.txt.)

Unfortunately, I’m not (yet?) intelligent enough to chase this through
the perl, but I’m hopeful this might narrow the problem domain enough
that someone who’s familiar with RT and SearchBuilder is able to
recognize what’s happening, and suggest a fix for this pernicious bug
that’s plagued RT for so long…

/Ole Craig
Security Engineer
Team lead, customer support

ocraig@stillsecure.com
303-381-3824 direct
303-381-3802 support
303-381-3880 fax

www.stillsecure.com

result-1.txt (3.42 KB)

result-3.txt (3.42 KB)

result-4.txt (3.51 KB)

sql-1.txt (1.02 KB)

sql-2.txt (1.05 KB)

sql-3.txt (1.16 KB)

sql-4.txt (1.19 KB)

result-2.txt (12.8 KB)