strange result from query, bug ?

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

Responses

Browse pgsql-general by date

  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?