Re: strange result from query, bug ?

Ahh yes, I understand now.

Thanks !

Best Regards
Dan S

2014-07-28 18:28 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Dan S <strd911(at)gmail(dot)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
>