Subselects in select expressions

From: Royce Ausburn <royce(dot)ml(at)inomial(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Subselects in select expressions
Date: 2011-11-02 01:42:56
Message-ID: 20AA4331-3EFF-437A-A787-45E49F33CE96@inomial.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm having trouble understanding why these two queries produce different results:

test=# select (select random()) from generate_series(1,10); -- rows are the same
?column?
-------------------
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
(10 rows)

test=# select (select random() where generate_series is not null) from generate_series(1,10); -- rows are different
?column?
--------------------
0.561828337144107
0.0275383000262082
0.290950470604002
0.281174722127616
0.530742571223527
0.617655908688903
0.169655770529062
0.428002137690783
0.442224354483187
0.203044794034213
(10 rows)

I understand that it's likely an optimisation thing -- postgres knows that the subselect doesn't depend on the FROM rows so it evaluates it only once, but is this really correct behaviour? Ideally, shouldn't postgres know that each invocation of random() produces different results and so decide that it should execute it for each row? If not, why?

Thanks!

--Royce

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric Smith 2011-11-02 02:05:17 Re: adding a column takes FOREVER!
Previous Message Jaime Casanova 2011-11-02 01:18:51 Re: Disable Streaming Replication ==> Stop WAL Sender on master and WAL receiver on Slave