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

From: Ulrich Meis <u(dot)meis(at)gmx(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: select where id=random()*something returns two results
Date: 2003-09-19 00:42:52
Message-ID: 000501c37e46$f58ffa50$24308286@DAYWALK
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

========================================================================
====
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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Ulrich Meis 2003-09-19 00:49:18 select where id=random()*something returns two results
Previous Message Josh Eno 2003-09-18 15:40:38 Can't access table to describe, drop, or select, but it does exist