Hi,
I'm running "PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by
gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit"
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 ?
Best Regards
Dan S
P.S.
I've since rewritten the query like below to get the expected results but I
still thought I should ask if it is a bug.
with
cte as
(
select generate_series,(random()*999.0)::int + 1 as id from
generate_series(1,1000)
)
select (select id from random_draw where random_draw.id=cte.id) as
rnd_id,random(),generate_series
from cte
