Thread: Bug? Me or PostgreSQL.

Greetings,

How can this happen?

chris=# select name from boys_names where 
boys_names.number=(random()*225+1)::int4; name
--------FredGunnarManuelRainer
(4 rows)

Here is the table declaration:

create table boys_names (       number                  serial primary key,       name                    varchar(20)
   ); 
 

the details of my installation:-

chris=# select version();                               version
------------------------------------------------------------------------PostgreSQL 7.1beta3 on i586-pc-linux-gnu,
compiledby GCC egcs-2.91.66
 
(1 row)

-- 
Sincerely etc.,
NAME       Christopher SawtellCELL PHONE 021 257 4451ICQ UIN    45863470EMAIL      csawtell @ xtra . co . nzCNOTES
ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me 
<<--

Christopher Sawtell <csawtell@xtra.co.nz> writes:
> How can this happen?

> chris=# select name from boys_names where 
> boys_names.number=(random()*225+1)::int4;
>   name
> --------
>  Fred
>  Gunnar
>  Manuel
>  Rainer
> (4 rows)

Er, what's your complaint exactly?  It's not obvious to me that
there's anything wrong here.  You seem to have gotten random
matches on four rows --- if that's ridiculously too low (or too
high) you'd better explain why you think so.
        regards, tom lane

On Mon, 19 Feb 2001, Christopher Sawtell wrote:

> How can this happen?
>
> chris=# select name from boys_names where
> boys_names.number=(random()*225+1)::int4;
>   name
> --------
>  Fred
>  Gunnar
>  Manuel
>  Rainer
> (4 rows)

It's not clear what you expected to see -- I'll guess you expected only
one row returned.

But random() is being called once for every row in the table -- it's not
surprising (if you have a lot of rows) that it comes up with a match more
than once.  Sometimes it might even return no rows at all.

SQL is not the best language for picking random members of a set.  You're
better off picking a random number in a procedural language and passing
that number as a constant to an embedded SQL query.
-- 
Tod McQuillin