Re: Ordering in an aggregate -- points to paths

"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
> OK, I know relying on ordering in an aggregate is sinful, but I don't know
> if it's mortal or venial.
> ...
> SELECT c.fir_ident, c.fir_indicator, create_path (c.node) AS fir_edge
>        INTO fir_e
>        FROM
>        (SELECT fir_ident, fir_indicator, node
>                FROM fir_coords
>                ORDER BY fir_ident, fir_indicator,seq_no) c
> GROUP BY fir_ident, fir_indicator;

Yeah, this is a fairly obvious thing to want to do with a user-written
aggregate.  It does not work in released versions, because the planner
does not notice that the inner SELECT's output ordering matches what
the GROUP BY needs, and so it inserts an additional Sort plan step
above the sub-select (you can see this if you look at EXPLAIN output).
Unfortunately, on most platforms qsort() isn't stable and will not
preserve the ordering of its input for equal keys.  So you lose the
minor ordering by seq_no in the re-sort.

We have fixed this in CVS tip by teaching the planner to notice the
subselect's result ordering and avoid the redundant Sort step.  The
patch is probably too large to consider back-patching into 7.3,
unfortunately.  Here's the log entry if you want to pursue that:

2003-02-15 15:12  tgl
* src/:
backend/optimizer/path/allpaths.c,backend/optimizer/path/pathkeys.c,backend/optimizer/plan/planner.c,backend/optimizer/util/pathnode.c,backend/optimizer/util/relnode.c,
backend/optimizer/util/tlist.c,include/optimizer/pathnode.h,include/optimizer/paths.h,include/optimizer/tlist.h: Teach
plannerhow to propagate pathkeysfrom sub-SELECTs in FROM up to the outer query.  (Theimplementation is a bit klugy, but
itwould take nontrivialrestructuring to make it nicer, which this is probably not worth.) This avoids unnecessary sort
stepsin examples like SELECTfoo,count(*) FROM (SELECT ... ORDER BY foo,bar) sub GROUP BY foowhich means there is now a
reasonabletechnique for controlling theorder of inputs to custom aggregates, even in the grouping case.
 
        regards, tom lane