strange result from query, bug ?
| От | Dan S |
|---|---|
| Тема | strange result from query, bug ? |
| Дата | |
| Msg-id | CAPpdapeOn6rabC0x=WMZ-USOP0_+z-0TAOoCz6jc-MixYRK4+A@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: strange result from query, bug ?
|
| Список | pgsql-general |
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.
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);
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)********** Error **********
ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000
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
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
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
В списке pgsql-general по дате отправления: