Re: [HACKERS] distinct + order by

I said:
> If we did want to make this example behave in a rational way, then
> probably the right implementation is something like
>     * sort by i,j
>     * distinct-filter on i only, being careful to keep first row
>         in each set of duplicates
>     * sort by j
> This would ensure that the final sort by j uses, for each distinct i,
> the lowest of the j-values associated with that i.  This is a totally
> arbitrary decision, but at least it will give reproducible results.

Some closer probing with "explain verbose" shows that
"SELECT DISTINCT i FROM dtest ORDER BY j" is actually transformed
into this:

Unique on i,j  (cost=1.10 size=0 width=0) ->  Sort by i,j  (cost=1.10 size=0 width=0)       ->  Seq Scan on dtest
selectingi,j  (cost=1.10 size=3 width=16)
 

This explains why you get the apparently duplicate i values --- they're
not duplicate when both i and j are considered.

It looks to me like someone tried to make the query tree builder deal
with this case in the way I suggest above, but didn't finish the job.
The "Unique" pass is being done on the wrong targets, and there's no
final sort.
        regards, tom lane