Re: Problem with ORDER BY and DISTINCT ON

At 07:29 AM 7/16/2008, Tom Lane wrote:
>Steve Midgley <public(at)misuse(dot)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