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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026