From: | Dan S <strd911(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | strange result from query, bug ? |
Date: | 2014-07-28 16:18:14 |
Message-ID: | CAPpdapeOn6rabC0x=WMZ-USOP0_+z-0TAOoCz6jc-MixYRK4+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-07-28 16:28:25 | Re: strange result from query, bug ? |
Previous Message | Joe Conway | 2014-07-28 15:29:42 | Re: Pairwise array sum aggregate function? |