Thread: Unexpected Result in Windowing

Folks,

Robert Treat brought this up.

I create a table with something to partition by, and some meaningless
junk.  I'm trying to get a random ordering inside each window:

CREATE TABLE foo(i, t) AS
SELECT i, md5((i*generate_series(1,10))::text) /* Nonsense text */
FROM generate_series(10,1) i;

SELECT   i,   t,   RANK() OVER (       PARTITION BY i       ORDER BY random()   )
FROM foo;
ERROR:  ORDER/GROUP BY expression not found in targetlist

Oops.  I found this error message only in backend/optimizer/util/tlist.c
and am wondering whether there needs to be a different code path for
windowing, or...?

Any ideas?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> SELECT
>     i,
>     t,
>     RANK() OVER (
>         PARTITION BY i
>         ORDER BY random()
>     )
> FROM foo;
> ERROR:  ORDER/GROUP BY expression not found in targetlist

Fixed.
        regards, tom lane

On Mon, Mar 30, 2009 at 01:31:23PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > SELECT
> >     i,
> >     t,
> >     RANK() OVER (
> >         PARTITION BY i
> >         ORDER BY random()
> >     )
> > FROM foo;
> > ERROR:  ORDER/GROUP BY expression not found in targetlist
> 
> Fixed.

Thanks! :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Monday 30 March 2009 15:34:49 David Fetter wrote:
> On Mon, Mar 30, 2009 at 01:31:23PM -0400, Tom Lane wrote:
> > David Fetter <david@fetter.org> writes:
> > > SELECT
> > >     i,
> > >     t,
> > >     RANK() OVER (
> > >         PARTITION BY i
> > >         ORDER BY random()
> > >     )
> > > FROM foo;
> > > ERROR:  ORDER/GROUP BY expression not found in targetlist
> >
> > Fixed.
>
> Thanks! :)
>

Yes, thanks!

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com