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
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 |