Re: Problem with ORDER BY and DISTINCT ON

At 07:29 AM 7/16/2008, Tom Lane wrote:
>Steve Midgley <public@misuse.org> writes:
>Interesting.  You realize of course that sorting by the same 
>expression
>twice is completely redundant?  I haven't dug through the code yet but
Thanks Tom. Yeah, I was a little embarrassed to throw this code up on 
the list b/c it's pretty weak. It's basically machine written - so 
sometimes the CASE features a different "THEN X ELSE Y" order.

>I think what is happening is that ORDER BY knows that and gets rid of
>the duplicate entries while DISTINCT ON fails to do so.  Or some story
>approximately like that.  It should be fixed, but the immediate
>workaround is just to get rid of the redundant sort keys:
I don't know if this will help track down the problem, but I figured 
out last night that ORDER BY is totally fine with having TWO duplicate 
entries, so long as I only put ONE entry in the DISTINCT ON area.

Of course removing the duplicate from both areas is the correct 
solution and I broke down and hacked that into the auto-sql-writing 
code and so my immediate problem is solved. I'm happy to file this as a 
ticket for Pg (please point me to your ticket tool as I've never used 
it). This is not a very big deal but Pg has such a high compliance with 
wacky-but-valid SQL it does seem like it should be fixed just because. 
Let me know if I can help on that.

Best,

Steve