Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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.



pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group