I’ve been struggling with CustomFields in RT not being shown in the desired order, and I have now pinpointed where the problem apparently is.
As I see it, it is Oracle’s query optimizer that prevents the DistinctQuery-SQL to return the rows in correct order. I have tweaked the generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the correct result (at least my CustomFields are now shown in the right order :)). You may argue, that there is an overhead in my solution (two order bys), but I do not have enough in-depth knowledge of how the whole SearchBuilder works.
Anyway - you may adopt the solution or simply use it as inspiration
diff DBIx-SearchBuilder-1.54/SearchBuilder/Handle/Oracle.pm Oracle.pm
275c275,283
< $$statementref = “SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)”;
$$statementref = qq [
SELECT main.* FROM
( SELECT rt_internal_table.id, rownum rt_internal_sort_order FROM
( SELECT main.id FROM $$statementref $group $order ) rt_internal_table
) distinctquery, $table main
WHERE (main.id = distinctquery.id) order by distinctquery.rt_internal_sort_order
];
Configuration used: Perl v5.8.8, Oracle 10.2.0.1.0, RT3.8.1 and SearchBuilder 1.54
Steen, I’ve bounced this into the RT bugs queue for evaluation. Thanks!On Thu 12.Feb’09 at 11:37:02 +0100, Steen Olesen wrote:
Hi,
I’ve been struggling with CustomFields in RT not being shown in the
desired order, and I have now pinpointed where the problem apparently is.
As I see it, it is Oracle’s query optimizer that prevents the
DistinctQuery-SQL to return the rows in correct order. I have tweaked the
generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the
correct result (at least my CustomFields are now shown in the right order
J). You may argue, that there is an overhead in my solution (two order
bys), but I do not have enough in-depth knowledge of how the whole
SearchBuilder works.
Anyway - you may adopt the solution or simply use it as inspiration J
I did look for solutions (and discussions on the subject) before trying to solve this.
It is quite obvious, that it is the Oracle query optimizer that is playing tricks.
You can run the standard DistinctQuery-SQL with:
Alter session set optimizer_mode=choose;
Get one result
Alter session set optimizer_mode=all_rows;
Get another result
Alter session set optimizer_mode=rule; // I know - this is not supported
Get a third result.
/SteenFra: rt-devel-bounces@lists.bestpractical.com [rt-devel-bounces@lists.bestpractical.com] På vegne af Stephen Turner [sturner@MIT.EDU]
Sendt: 12. februar 2009 18:58
Til: Jesse Vincent; Steen Olesen
Cc: rt-devel@lists.bestpractical.com
Emne: Re: [Rt-devel] SearchBuilder and sorting in Oracle
Steen, I’ve bounced this into the RT bugs queue for evaluation. Thanks!
There was some discussion about this last year:
Steve
Stephen Turner
Senior Programmer/Analyst - SAIS
MIT IS&T
List info: The rt-devel Archives
