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 selecting i,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