From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) |
Cc: | t-ishii(at)sra(dot)co(dot)jp, hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] distinct + order by |
Date: | 1998-12-12 20:33:13 |
Message-ID: | 199812122033.PAA04155@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-12-12 20:57:09 | Re: OK now :-) was Re: [HACKERS] regression tests |
Previous Message | Bruce Momjian | 1998-12-12 20:21:15 | Re: New SPI contrib stuff, was Re: [HACKERS] SPI hacking |