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.

I have added this to TODO:

* SELECT DISTINCT i FROM dtest ORDER BY j generates strange output

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026