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
