Ahh yes, I understand now. Thanks !
Best Regards Dan S 2014-07-28 18:28 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>: > Dan S <strd...@gmail.com> writes: > > I've run into a strange problem with a query. > > I wonder if it might be a bug or a misunderstanding from my side. > > > Steps to recreate the problem: > > > Generate the necessary test data: > > create table random_draw( id int not null , constraint random_draw_id_pk > > primary key(id)); > > > insert into random_draw > > select * > > from generate_series(1,1000); > > > Run this query several times: > > select (select id from random_draw where id=((random()*999.0)::int)+1) as > > rnd_id, random(), * > > from generate_series(1,1000); > > > The query sometimes give the error: > > ERROR: more than one row returned by a subquery used as an expression > > ********** Error ********** > > > ERROR: more than one row returned by a subquery used as an expression > > SQL state: 21000 > > > somtimes the rnd_id column is null and sometimes it gives an expected > > answer (an integer between 1 and 1000) > > > Why does it sometimes say it returned more than one row ? > > Why does it sometimes give null in rnd_id column ? > > I would have expected the subquery get reexecuted for each row from > > generate_series > > because the random() function in the where clause expression is volatile > ? > > The problem with this query is that the random() call in the subquery is > executed again *for each row of random_draw*. So the subquery is not > selecting a single randomly-chosen row of random_draw; it's choosing each > row of the table with probability 1/1000. So sometimes you get no row > selected or more than one row selected. > > The CTE solution is one way to fix this. There are lots of others. > > AFAIK this behavior is required by SQL standard: notionally, the WHERE > clause is to be evaluated for each row of the FROM table(s). In many > cases the planner can optimize that, but not when it's dealing with a > volatile function in WHERE. > > regards, tom lane >
