Dan S <strd911@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.
