[Rt-devel] Sortorder of customfields

[Joop van de Wege]

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