Thread: select where id=random()*something returns two results

select where id=random()*something returns two results

From
Ulrich Meis
Date:
========================================================================
====
POSTGRESQL BUG REPORT TEMPLATE
========================================================================
====


Your name        :    Ulrich Meis
Your email address    :    u.meis ( at ) gmx ( dot ) de


System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD XP 1ghz

Operating System (example: Linux 2.0.26 ELF) : Linux (6month old) Gentoo

PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL 7.3.4

Compiler used (example: gcc 2.95.2) : gnu gcc 3.2.2


Please enter a FULL description of your problem:
------------------------------------------------
A "select * from table where primkey=..." sometimes returns two results.
Having a table of quotes created as can be seen in the next section,
I've seen the following in psql:

select * from quotes where id=1+round(random()* cast ((select max(id)
from quotes) as double precision));
 id  |                           quote                           |
author
-----+-----------------------------------------------------------+------
-----------
 187 | Vergib Deinen Feinden, aber vergiss niemals ihre Namen.   | John
F. Kennedy
 377 | Die Wirklichkeit ist nicht so oder so, sondern so und so. | Harry
Mulisch
(2 rows)

I'm not really into databases, but this sounds wrong. Most of the time,
I actually get 0 results.
This should be impossible as well, because I filled the table up without
touching the serial and without
deleting a single row.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create table quotes (id serial,quote text,author text);

Fill in some quotes...

select * from quotes where id=1+round(random()* cast ((select max(id)
from quotes) as double precision));

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
No idea.