Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Ron Mayer 
Andrew Gierth wrote:
 This query:
 
 select random() from generate_series(1,10) order by random();
 produces sorted output. Should it?

I recall a workaround from a different thread[1] if specifically
were looking for random ordering of random numbers is:
select random() from foo order by random()+1;

The thread has more odd corner cases with multiple calls
to random() and sorts as well.


[1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Tom Lane 
Andrew Gierth and...@tao11.riddles.org.uk writes:
 For bonus weirdness:

 select distinct random(),random() from generate_series(1,10);
 set enable_hashagg=off;
 select distinct random(),random() from generate_series(1,10);

 I think _that_ one is a bug.

Hmm.  I think the first one is a bug --- the two invocations of
random() in the tlist shouldn't be folded together.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Andrew Gierth 
 Tom == Tom Lane t...@sss.pgh.pa.us writes:

  For bonus weirdness:

  select distinct random(),random() from generate_series(1,10);
  set enable_hashagg=off;
  select distinct random(),random() from generate_series(1,10);

  I think _that_ one is a bug.

 Tom Hmm.  I think the first one is a bug --- the two invocations of
 Tom random() in the tlist shouldn't be folded together.

That's what I meant.

If you try it using nextval(), you'll notice that the function does
in fact get called twice per row, but one of the results is thrown
away and replaced with the other one.

-- 
Andrew.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Tom Lane 
Andrew Gierth and...@tao11.riddles.org.uk writes:
 If you try it using nextval(), you'll notice that the function does
 in fact get called twice per row, but one of the results is thrown
 away and replaced with the other one.

Yeah.  The problem is that setrefs.c is generating a tlist for the
hashagg node in which both output expressions point to the first
output of the underlying scan node, because it's just relying on
equal() to match up the expressions.  I'm testing a fix now ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Tom Lane 
Ron Mayer rm...@cheapcomplexdevices.com writes:
 [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php

FWIW, the behavior has changed from the time of that discussion ---
we now track sort ordering using EquivalenceClasses, which are able
to distinguish different instances of textually equal() volatile
expressions.  The particular cases of
select random() from foo order by 1;
select random() from foo order by random();
still behave the same, but that's intentional for backwards
compatibility (and because SQL99 forbids the first, which would mean
there's no way to get the behavior except via deprecated syntax).
Beyond the case of order by/group by items being matched to tlist
items, I'd generally expect that the system should act as though
different textual instances of random() are evaluated separately.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Andrew Gierth 
 Tom == Tom Lane t...@sss.pgh.pa.us writes:

 Tom Ron Mayer rm...@cheapcomplexdevices.com writes:
  [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php

 Tom FWIW, the behavior has changed from the time of that discussion ---
 Tom we now track sort ordering using EquivalenceClasses, which are able
 Tom to distinguish different instances of textually equal() volatile
 Tom expressions.  The particular cases of
 Tom   select random() from foo order by 1;
 Tom   select random() from foo order by random();
 Tom still behave the same, but that's intentional for backwards
 Tom compatibility (and because SQL99 forbids the first, which would mean
 Tom there's no way to get the behavior except via deprecated syntax).

SQL99 doesn't forbid:

select random() as r from foo order by r;

or

select r from (select random() as r from foo) as s order by r;

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ORDER BY vs. volatile functions

2009-11-15 Thread Andrew Gierth 
This query:

select random() from generate_series(1,10) order by random();

produces sorted output. Should it?

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-15 Thread Tom Lane 
Andrew Gierth and...@tao11.riddles.org.uk writes:
 This query:
 select random() from generate_series(1,10) order by random();
 produces sorted output.
 Should it?

It always has; we'd doubtless break some apps if we changed that.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-15 Thread Andrew Gierth 
 Tom == Tom Lane t...@sss.pgh.pa.us writes:

  This query:
  select random() from generate_series(1,10) order by random();
  produces sorted output.
  Should it?

 Tom It always has; we'd doubtless break some apps if we changed that.

For bonus weirdness:

select distinct random(),random() from generate_series(1,10);
set enable_hashagg=off;
select distinct random(),random() from generate_series(1,10);

I think _that_ one is a bug.

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers