Thread

1. Unexpected Result in Windowing

   David Fetter <david@fetter.org> — 2009-03-30T01:35:16Z

   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
   
   

2. Re: Unexpected Result in Windowing

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-03-30T17:31:23Z

   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
   
   

3. Re: Unexpected Result in Windowing

   David Fetter <david@fetter.org> — 2009-03-30T19:34:49Z

   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
   
   

4. Re: Unexpected Result in Windowing

   Robert Treat <xzilla@users.sourceforge.net> — 2009-03-31T05:16:15Z

   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